You can use the grant command to grant permission on objects to all users who have been granted a specified role, whether system or user-defined. This allows you to restrict use of an object to users who have been granted any of these roles:
System Administrator
System Security Officer
Operator
Any user-defined role
A role can only be granted to a login account or another role.
However, grant permission does not prevent users who do not have the specified role from being granted execute permission on a stored procedure. If you want to ensure, for example, that only System Administrators can successfully execute a stored procedure, use the proc_role system function within the stored procedure itself. See “Displaying information about roles” for more information.
Permissions granted to roles override permissions granted to users or groups. For example, assume John has been granted the System Security Officer role, and sso_role has been granted permission on the sales table. If John’s individual permission on sales is revoked, he is still able to access sales when he has sso_role active because his role permissions override his individual permissions.
In granting permissions, a System Administrator is treated as the object owner. If a System Administrator grants permission on another user’s object, the owner‘s name appears as the grantor in sysprotects and in sp_helprotect output.
If several users grant access to an object to a particular user, the user’s access remains until access is revoked by all those who granted access. If a System Administrator revokes access, the user is denied access, even though other users have granted access.
