Granting Permissions

Grant statement and object permissions that allow a user account to:

• Perform activities or work with data in the current database.
  
  
• 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, grant SELECT access to personnel for all columns in payroll except the salary column.

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)

Transact-SQL