The database owner, members of sysadmin, and members of securityadmin can assign database permissions. The available permissions include the following:
GRANT. Gives permission to perform the related task. With roles, all members of the role inherit the permission.
REVOKE. Removes prior GRANT permission but does not explicitly prevent a user or role from performing a task. A user or role could still inherit GRANT permission from another role.
DENY. Explicitly denies permission to perform a task and prevents the user or role from inheriting the permission. DENY takes precedence over all other grant permissions.
You can grant, deny, and revoke permissions at the database level or the object level. You can also assign permissions using database roles. For more information, see the section titled Controlling Database Access and Administration earlier in this chapter.
At the database level, you can grant, revoke, or deny permission to execute data definition language statements, such as CREATE TABLE or BACKUP DATABASE. These statements were summarized in Table 8-5 earlier in this chapter.
In SQL Server Management Studio, you grant, revoke, or deny database permissions for statements by completing the following steps:
Start SQL Server Management Studio. In Object Explorer view, connect to the appropriate server.
Work your way down to the Databases folder using the entries in the left pane.
Select a database, right-click the database name, and then select Properties from the shortcut menu to display the Database Properties dialog box.
Select the Permissions page from the Select A Page list, as shown in Figure 8-9.
To assign default permissions for all users, assign permissions to the public role. To add users or roles, click Add and then use the Select Users Or Roles dialog box to select the user or role you want to add. To assign permissions for individual users or roles, select the user or role, and then use the Permissions For ... list box to Allow or Deny permissions as appropriate. Clear both check marks to revoke a previously granted or denied permission.
Click OK to assign the permissions.
With Transact-SQL, you use the GRANT, REVOKE, and DENY commands to assign permissions. Example 8-12 shows the syntax and usage for GRANT, Example 8-13 shows the syntax and usage for REVOKE, and Example 8-14 shows the syntax and usage for DENY.
Example 8-12. GRANT Syntax and Usage
Syntax for Permissions on Servers and Databases
GRANT < permission > [ ,...n ]
TO < principal > [ ,...n ] [ WITH GRANT OPTION ]
[ AS
{
Windows_group | SQL_Server_login | database_user
| database_role | application_role
}
]
< permission >::= ALL [ PRIVILEGES ] | permission_name
[ ( column [ ,...n ] ) ]
< principal >::= Windows_login | SQL_Server_login
| SQL_Server_login_mapped_to_certificate
| SQL_Server_login_mapped_to_asymmetric_key
| Database_user | Database_role | Application_role
| Database_user_mapped_to_certificate
| Database_user_mapped_to_asymmetric_keySyntax for Permissions on Members of the Object Class
GRANT < permission > [ ,...n ] ON [ OBJECT ::] < securable_name >
TO < principal > [ ,...n ] [ WITH GRANT OPTION ]
[ AS
{
Windows_group | SQL_Server_login | database_user
| database_role | application_role
}
]
< permission >::= ALL [ PRIVILEGES ] | permission_name
[ ( column [ ,...n ] ) ]
< principal >::= Windows_login | SQL_Server_login
| SQL_Server_login_mapped_to_certificate
| SQL_Server_login_mapped_to_asymmetric_key
| Database_user | Database_role | Application_role
| Database_user_mapped_to_certificate
| Database_user_mapped_to_asymmetric_keySyntax for Permissions on All Other Securables
GRANT < permission > [ ,...n ] ON < scope >
TO < principal > [ ,...n ] [ WITH GRANT OPTION ]
[ AS
{
Windows_group | SQL_Server_login | database_user
| database_role | application_role
}
]
< permission >::= ALL [ PRIVILEGES ] | permission_name
[ ( column [ ,...n ] ) ]
< scope >::= [ securable_class :: ] securable_name
< securable_class >::= APPLICATION ROLE | ASSEMBLY | ASYMMETRIC KEY
| CERTIFICATE | CONTRACT | ENDPOINT | FULLTEXT CATALOG
| LOGIN | MESSAGE TYPE | REMOTE SERVICE BINDING | ROLE
| ROUTE | SCHEMA | SERVICE | SYMMETRIC KEY | TYPE
| USER | XML SCHEMA COLLECTION
< principal >::= Windows_login | SQL_Server_login
| SQL_Server_login_mapped_to_certificate
| SQL_Server_login_mapped_to_asymmetric_key
| Database_user | Database_role | Application_role
| Database_user_mapped_to_certificate
| Database_user_mapped_to_asymmetric_keyUsage
GRANT CREATE DATABASE, CREATE TABLE TO Users, [GALAXYSales] GRANT SELECT ON customer..customers TO public GRANT INSERT, UPDATE, DELETE ON customer..customers TO Devs, Testers
Example 8-13. REVOKE Syntax and Usage
Syntax for Permission on Servers and Databases
REVOKE [ GRANT OPTION FOR ] < permission > [ ,...n ]
{ TO | FROM } < principal > [ ,...n ] [ CASCADE ]
[ AS
{
Windows_group | SQL_Server_login
| database_role | application_role
}
]
< permission >::= ALL [ PRIVILEGES ] | permission_name
[ ( column [ ,...n ] ) ]
< principal >::= Windows_login | SQL_Server_login
| SQL_Server_login_mapped_to_certificate
| SQL_Server_login_mapped_to_asymmetric_key
| Database_user | Database_role | Application_role
| Database_user_mapped_to_certificate
| Database_user_mapped_to_asymmetric_keySyntax for Permissions on Members of the Object Class
REVOKE [ GRANT OPTION FOR ] < permission > [ ,...n ]
ON [ OBJECT ::] < securable_name >
{ TO | FROM } < principal > [ ,...n ] [ CASCADE ]
[ AS
{
Windows_group | SQL_Server_login
| database_role | application_role
}
]
< permission >::= ALL [ PRIVILEGES ] | permission_name
[ ( column [ ,...n ] ) ]
< principal >::= Windows_login | SQL_Server_login
| SQL_Server_login_mapped_to_certificate
| SQL_Server_login_mapped_to_asymmetric_key
| Database_user | Database_role | Application_role
| Database_user_mapped_to_certificate
| Database_user_mapped_to_asymmetric_keySyntax for Permissions on All Other Securables
REVOKE [ GRANT OPTION FOR ] < permission > [ ,...n ] [ ON < scope > ]
{ TO | FROM } < principal > [ ,...n ] [ CASCADE ]
[ AS
{
Windows_group | SQL_Server_login
| database_role | application_role
}
]
< permission >::= ALL [ PRIVILEGES ] | permission_name
[ ( column [ ,...n ] ) ]
< scope >::= [ < securable_class > :: ] securable_name
< securable_class >::= APPLICATION ROLE | ASSEMBLY | ASYMMETRIC KEY
| CERTIFICATE | CONTRACT | ENDPOINT | FULLTEXT CATALOG
| LOGIN | MESSAGE TYPE | REMOTE SERVICE BINDING | ROLE
| ROUTE | SCHEMA | SERVICE | SYMMETRIC KEY | TYPE
| USER | XML SCHEMA COLLECTION
< principal >::= Windows_login | SQL_Server_login
| SQL_Server_login_mapped_to_certificate
| SQL_Server_login_mapped_to_asymmetric_key
| Database_user | Database_role | Application_role
| Database_user_mapped_to_certificate
| Database_user_mapped_to_asymmetric_keyUsage
REVOKE CREATE TABLE, CREATE DEFAULT FROM Devs, Testers REVOKE INSERT, UPDATE, DELETE FROM Users, [GALAXYSales]
Example 8-14. DENY Syntax and Usage
Syntax for Permission on Servers and Databases
DENY < permission > [ ,...n ]
TO < principal > [ ,...n ] [ CASCADE ]
[ AS
{
Windows_group | SQL_Server_login | database_user
| database_role | application_role
}
]
< permission >::= ALL [ PRIVILEGES ] | permission_name
[ ( column [ ,...n ] ) ]
< principal >::= Windows_login | SQL_Server_login
| SQL_Server_login_mapped_to_certificate
| SQL_Server_login_mapped_to_asymmetric_key
| Database_user | Database_role | Application_role
| Database_user_mapped_to_certificate
| Database_user_mapped_to_asymmetric_keySyntax for Permissions on Members of the Object Class
DENY < permission > [ ,...n ] ON [ OBJECT ::] < securable_name >
TO < principal > [ ,...n ] [ CASCADE ]
[ AS
{
Windows_group | SQL_Server_login | database_user
| database_role | application_role
}
]
< permission >::= ALL [ PRIVILEGES ] | permission_name
[ ( column [ ,...n ] ) ]
< principal >::= Windows_login | SQL_Server_login
| SQL_Server_login_mapped_to_certificate
| SQL_Server_login_mapped_to_asymmetric_key
| Database_user | Database_role | Application_role
| Database_user_mapped_to_certificate
| Database_user_mapped_to_asymmetric_keySyntax for Permissions on All Other Securables
DENY < permission > [ ,...n ] ON < scope >
TO < principal > [ ,...n ] [ CASCADE ]
[ AS
{
Windows_group | SQL_Server_login | database_user
| database_role | application_role
}
]
< permission >::= ALL [ PRIVILEGES ] | permission_name
[ ( column [ ,...n ] ) ]
< scope >::= [ securable_class :: ] securable_name
< securable_class >::= APPLICATION ROLE | ASSEMBLY | ASYMMETRIC KEY
| CERTIFICATE | CONTRACT | ENDPOINT | FULLTEXT CATALOG
| LOGIN | MESSAGE TYPE | REMOTE SERVICE BINDING | ROLE
| ROUTE | SCHEMA | SERVICE | SYMMETRIC KEY | TYPE
| USER | XML SCHEMA COLLECTION
< principal >::= Windows_login | SQL_Server_login
| SQL_Server_login_mapped_to_certificate
| SQL_Server_login_mapped_to_asymmetric_key
| Database_user | Database_role | Application_role
| Database_user_mapped_to_certificate
| Database_user_mapped_to_asymmetric_keyUsage
DENY CREATE TABLE TO Devs, Testers DENY INSERT, UPDATE, DELETE ON customer..customers TO Users, [GALAXYSales]
Object permissions apply to tables, views, and stored procedures. Permissions you assign to these objects include SELECT, INSERT, UPDATE, and DELETE. A summary of permitted actions by object was provided in Table 8-4 earlier in the chapter.
In SQL Server Management Studio, you grant, revoke, or deny object permissions by completing the following steps:
Start SQL Server Management Studio. In Object Explorer view, connect to the appropriate server.
Work your way down to the Databases folder using the entries in the left pane.
Expand the Databases folder, and then select a database and expand the node for it.
Expand the Security and Users folders.
Double-click the user you want to configure to display the Database User dialog box.
Select the Securables page from the Select A Page list as shown in Figure 8-10.
To assign object permissions, click Add to display the Add Objects dialog box.
In the Add Objects dialog box, select the type of objects for which you want to manage permissions:
Select Specific Objects if you know the name of the objects for which you want to manage permissions, and then click OK. In the Select Objects dialog box, click Object Types. Next, in the Select Object Types dialog box, select the type of objects to find, such as Tables And Views, and then click OK. In the Select Objects dialog box, enter the object names. Separate multiple names with a semicolon, and click Check Names to expand any partial name you enter to full names. To browse for objects of the previously selected type, click Browse. When you are finished selecting objects, click OK. The selected objects then will be listed in the Database User dialog box.
Select All Objects Of The Types if you want to manage permissions for all objects of a specific type, such as all tables and views, and then click OK. In the Select Objects dialog box, click Object Types. Next, in the Select Object Types dialog box, select the type of objects to find, such as Tables And Views, and then click OK. In the Select Object Types dialog box, select the object types you want to manage, and then click OK. All objects of the selected types then will be listed in the Database User dialog box.
Select All Objects Belonging To The Schema ... if you want to manage permissions for all objects owned by a particular schema. In the Add Objects dialog box, use the Schema Name selection menu to choose the schema whose objects you want to manage, and then click OK. All objects belonging to the schema will then be listed in the Database User dialog box.
To set permissions on an object for the currently selected user, select the object in the Objects list box, and then use the Permissions For... list box to Allow or Deny permissions as appropriate. Clear both check marks to revoke a previously granted or denied permission.
When you are finished, click OK to assign the permissions.
You can also assign permission by object, and in this way assign object permissions for multiple logins. To do this, complete the following steps:
Work your way down to the Databases folder using the entries in the left pane.
Expand the Databases folder, and then select the folder for the type of objects you want to work with, such as Tables, Views, or Stored Procedures.
Double-click the object you want to configure to display a Properties dialog box. Or right-click and select Properties.
In the Properties dialog box, select the Permissions page from the Select A Page list. This opens the Object Properties dialog box, such as the Table Properties dialog box shown in Figure 8-11.
Any user or roles directly assigned permissions on the object are listed in the Users Or Roles list box.
To add specific permissions for users, roles, or both, click Add to open the Select Users Or Roles dialog box.
Enter the names of the users or roles to add. Separate names with semicolons. You can enter partial names, and then click Check Names to expand the names. To search for names, click Browse.
To set permissions for the object, select a user or role in the Users Or Roles list box, and then use the Permissions For list box to Allow or Deny permissions as appropriate. Clear both check marks to revoke a previously granted or denied permission.
When you are finished, click OK to assign the permissions.
More information about Transact-SQL commands for assigning permissions can be found in the section titled "Assigning Database Permissions for Statements" earlier in this chapter.