Managing Users Permissions on SQL Server
August 13, 2003
Permissions are the rights to access the database objects. Permissions can be granted to a user or role to allow that user or role to perform operations such as selection, insertion or modification of data rows.
Each database object has an owner. By default, the owner is the creator of an object, but the ownership can be transferred later after the object has been created. In addition to the owner, the members of the sysadmin fixed server roles have full permissions on all objects in all user and system databases.
There is also a public role. The public role is a special database role to which each database user belongs. The public role contains default access permissions for any user who can access the database. This database role cannot be dropped, but it is strongly recommended not to grant superfluous permissions to the public role, because each databases user has the public role's permissions.
SQL Server 2000 provides the GRANT, DENY, and REVOKE statements to give or take away permissions from a user or role.
To perform any activity in a database, user must have the appropriate permissions. These permissions fall into three categories, which we call permissions types:
SQL Server 2000 supports granting or revoking user rights to the following permissions types:
The object permissions are the permissions to act on the database objects (such as tables, stored procedures and views). They consist of the following permissions:
These are the permissions to create a database or an object in the database. These permissions are applied to the statement itself, rather than to a specific object defined in the database. They consist of the following permissions:
These are the permissions granted to the predefined roles (such as fixed server roles or fixed database roles). For example, a member of the db_owner fixed database role has full permissions in the database.
You can use the GRANT, DENY, and REVOKE statements to give or take away permission from a user or role.
The GRANT statement is used to give permissions to a user or role. By using the GRANT statement, it is possible to assign permissions to both statements as well as objects. You can use the GRANT statement with the WITH GRANT OPTION clause to permit the user or role receiving the permission to further grant/revoke access to other accounts.
This example grants the SELECT permission on the authors table to Alex
GRANT SELECT ON authors TO Alex
The DENY statement is used to deny a permission from a security account in the current database and prevents the security account from inheriting the permission through its group or role memberships. You can use the DENY statement to deny both statements and objects permissions.
The following example denies the user Alex SELECT permissions to the authors table:
DENY SELECT ON authors TO Alex
The REVOKE statement is used to remove a previously granted or denied permission from a user in the current database. You can use the REVOKE statement to remove both statements and objects permissions. You can specify the GRANT OPTION FOR clause with the REVOKE statement to remove the WITH GRANT OPTION permissions. Therefore, the user will have the objects permissions, but cannot grant the permissions to other users. Specify the CASCADE clause along with the WITH GRANT OPTION clause, if the permissions being revoked were originally granted using the WITH GRANT OPTION setting.
The following example revokes SELECT permissions to the authors table from the user, Alex:
REVOKE SELECT ON authors TO Alex
Use the WITH GRANT OPTION setting very carefully, because in this case users can grant permissions to the objects to other users and it will be more difficult to manage security.
Do not grant the superfluous permissions to the public role, because each database user has the public role's permissions.