To allow access by granting permissions (on an object)You can grant statement and object permissions that allow a user account to perform activities or work with data in the current database.
It is important to be careful with the permissions granted to a user or group, especially when working with large, complex security systems and sensitive data. You must be certain that the permissions allow the users to perform their necessary activities in the current database, but restrict them from activities or information not part of their intended function.
For example, you may be inclined to grant SELECT object permission on the payroll table to all members of the personnel role, allowing all members of personnel to view payroll. Months later, you may overhear members of personnel discussing management salaries, information not meant to be seen by all personnel members. In this situation, you would grant SELECT access to personnel for all columns in payroll except the salary column.
The same theory applies to statement permissions. You may have a project lead who needs a new database. You can grant CREATE DATABASE permission to the project lead, and then find later that all the space on the server has been used because the project lead has created many databases while experimenting with various database options. A better solution in this case would be to create a database for the project lead and then grant CREATE TABLE and CREATE PROCEDURE permissions to the project lead in the new database, or make the project lead a member of the db_owner fixed database role in the database.
Note It is possible to grant permissions only to user accounts in the current database, for objects in the current database. If a user needs permissions to objects in another database, create the user account in the other database, or grant the user account access to the other database, as well as the current database. System stored procedures are the exception because EXECUTE permissions are already granted to the public role, which allows everyone to execute them. However, after EXECUTE has been issued, the system stored procedures check the user’s role membership. If the user is not a member of the appropriate fixed server or database role necessary to run the stored procedure, the stored procedure will not continue.
To allow access by granting permissions (on an object)
To grant statement permissions to users within a database
To grant permissions on multiple objects to a user, group, or role