Permissions determine the actions that users can perform on SQL Server or in a database. Permissions are granted according to the login ID, group memberships, and role memberships. Users must have appropriate permissions before they can perform any action that changes database definitions or accesses data. Three types of permissions are used in SQL Server:
Object permissions
Statement permissions
Implicit permissions
In SQL Server 2005, all object permissions are grantable. You can manage permissions for specific objects, all objects of particular types, and all objects belonging to a specific schema. The objects for which you can manage permissions depend on the scope. At the server level, you can grant object permissions for servers, endpoints, logins, and server roles. You can also manage permissions for the current server instance.
At the database level, you can manage object permissions for application roles, assemblies, asymmetric keys, certificates, database roles, databases, full-text catalogs, functions, schemas, stored procedures, symmetric keys, synonyms, tables, user-defined data types, users, views, and XML schema collections.
You control access to these objects by granting, denying, or revoking the ability to execute particular statements or stored procedures. For example, you can grant a user the right to SELECT information from a table, but deny the right to INSERT, UPDATE, or DELETE information in the table. Table 8-4 provides a summary of object permissions.
Table 8-4. Object Permissions
Base Securable | Configurable Permissions | Highest Permission | Contained In | Implied Permission from Parent |
|---|---|---|---|---|
APPLICATION ROLE | ALTER, CONTROL, VIEW DEFINITION | CONTROL | DATABASE | ALTER ANY APPLICATION ROLE, CONTROL, VIEW DEFINITION |
ASSEMBLY | ALTER, CONTROL, EXECUTE, REFERENCES, TAKE OWNERSHIP, VIEW DEFINITION | CONTROL | DATABASE | ALTER ANY ASSEMBLY, CONTROL, EXECUTE, REFERENCES, VIEW DEFINITION |
ASYMMETRIC KEY | ALTER, CONTROL, REFERENCES, TAKE OWNERSHIP, VIEW DEFINITION | CONTROL | DATABASE | ALTER ANY ASYMMETRIC KEY, CONTROL, REFERENCES, VIEW DEFINITION |
CERTIFICATE | ALTER, CONTROL, REFERENCES, TAKE OWNERSHIP, VIEW DEFINITION | CONTROL | DATABASE | ALTER ANY CERTIFICATE, CONTROL, REFERENCES, VIEW DEFINITION |
CONTRACT | ALTER, CONTROL, REFERENCES, TAKE OWNERSHIP, VIEW DEFINITION | CONTROL | DATABASE | ALTER ANY CONTRACT, CONTROL, REFERENCES, VIEW DEFINITION |
DATABASE | ALTER, ALTER ANY APPLICATION ROLE, ALTER ANY ASSEMBLY, ALTER ANY ASYMMETRIC KEY, ALTER ANY CERTIFICATE, ALTER ANY CONTRACT, ALTER ANY DATABASE EVENT NOTIFICATION, ALTER ANY DATASPACE, ALTER ANY FULLTEXT CATALOG, ALTER ANY MESSAGE TYPE, ALTER ANY REMOTE SERVICE BINDING, ALTER ANY ROLE, ALTER ANY ROUTE, ALTER | CONTROL, CONNECT REPLICATION, ALTER ANY ASSEMBLY, ALTER ANY CERTIFICATE, ALTER ANY CONTRACT, ALTER ANY DATABASE EVENT NOTIFICATION, ALTER ANY | SERVER | ALTER ANY DATABASE, CONTROL SERVER, EXTERNAL ACCESS, CREATE ANY DATABASE, VIEW ANY DEFINITION |
SERVICE, ALTER ANY SYMMETRIC KEY, ALTER ANY TRIGGER, ALTER ANY USER, ALTER ANY XML SCHEMA COLLECTION, AUTHENTICATE, BACKUP DATABASE, BACKUP LOG, CHECKPOINT, CONNECT, CONNECT REPLICATION, CONTROL, CREATE AGGREGATE, CREATE ASSEMBLY, CREATE CERTIFICATE, CREATE CONTRACT, CREATE DATABASE, CREATE DATABASE EVENT NOTIFICATION, CREATE DEFAULT, CREATE FULLTEXT CATALOG, CREATE FUNCTION, CREATE MESSAGE TYPE, CREATE PROCEDURE, CREATE QUEUE, CREATE REMOTE SERVICE BINDING, CREATE ROLE, CREATE ROUTE, CREATE RULE, CREATE SCHEMA, CREATE SERVICE, CREATE SYMMETRIC KEY, CREATE SYNONYM, CREATE TABLE, CREATE TYPE, CREATE VIEW, CREATE XML SCHEMA COLLECTION, DELETE, EXECUTE, INSERT, REFERENCES, SELECT, SHOWPLAN, SUBSCRIBE QUERY NOTIFICATIONS, TAKE OWNERSHIP, UPDATE, VIEW DEFINITION | FULLTEXT CATALOG, ALTER ANY MESSAGE TYPE, ALTER ANY REMOTE SERVICE BINDING, ALTER ANY ROLE, ALTER ANY ROUTE, ALTER ANY SCHEMA, ALTER ANY SERVICE, ALTER ANY SYMMETRIC KEY, ALTER ANY XML SCHEMA COLLECTION | |||
ENDPOINT | ALTER, CONNECT, CONTROL, TAKE OWNERSHIP, VIEW DEFINITION | CONTROL | SERVER | ALTER ANY ENDPOINT, CONTROL SERVER |
FULLTEXT CATALOG | ALTER, CONTROL, REFERENCES, TAKE OWNERSHIP, VIEW DEFINITION | CONTROL | DATABASE | ALTER ANY FULLTEXT CATALOG, CONTROL, REFERENCES, VIEW DEFINITION |
LOGIN | ALTER, CONTROL, IMPERSONATE, VIEW DEFINITION | CONTROL | SERVER | ALTER ANY LOGIN, CONTROL SERVER |
MESSAGE TYPE | ALTER, CONTROL, REFERENCES, TAKE OWNERSHIP, VIEW DEFINITION | CONTROL | DATABASE | ALTER ANY MESSAGE TYPE, CONTROL, REFERENCES, VIEW DEFINITION |
OBJECT | ALTER, CONTROL, DELETE, EXECUTE, INSERT, RECEIVE, REFERENCES, SELECT, TAKE OWNERSHIP, UPDATE, VIEW DEFINITION | CONTROL | SCHEMA | ALTER, CONTROL, DELETE, EXECUTE, INSERT, RECEIVE, REFERENCES, SELECT, UPDATE, VIEW DEFINITION |
REMOTE SERVICE BINDING | ALTER, CONTROL, TAKE OWNERSHIP, VIEW DEFINITION | CONTROL | DATABASE | ALTER ANY REMOTE SERVICE BINDING, CONTROL, VIEW DEFINITION |
ROLE | ALTER, CONTROL, TAKE OWNERSHIP, VIEW DEFINITION | CONTROL | DATABASE | ALTER ANY ROLE, CONTROL, VIEW DEFINITION |
ROUTE | ALTER, CONTROL, TAKE OWNERSHIP, VIEW DEFINITION | CONTROL | DATABASE | ALTER ANY ROUTE, CONTROL, VIEW DEFINITION |
SCHEMA | ALTER, CONTROL, DELETE, EXECUTE, INSERT, REFERENCES, SELECT, TAKE OWNERSHIP, UPDATE, VIEW DEFINITION | CONTROL | DATABASE | ALTER ANY SCHEMA, CONTROL, DELETE, EXECUTE, INSERT, REFERENCES, SELECT, UPDATE, VIEW DEFINITION |
SERVER | ADMINISTER BULK OPERATIONS, ALTER ANY CONNECTION, ALTER ANY CREDENTIAL, ALTER ANY DATABASE, ALTER ANY ENDPOINT, ALTER ANY EVENT NOTIFICATION, ALTER ANY LINKED SERVER, ALTER ANY LOGIN, ALTER RESOURCES, ALTER SERVER STATE, ALTER SETTINGS, ALTER TRACE, AUTHENTICATE SERVER, CONTROL SERVER, CREATE ANY DATABASE, CREATE DDL EVENT, CREATE ENDPOINT, CREATE EVENT NOTIFICATION, CREATE MANAGEMENT EVENT, CREATE SECURITY EVENT, CREATE USER EVENT, EXTERNAL ACCESS, SHUTDOWN, VIEW ANY DEFINITION, VIEW SERVER STATE | CONTROL SERVER, ALTER ANY DATABASE, ALTER ANY EVENT NOTIFICATION, ALTER ANY ENDPOINT, ALTER SERVER STATE | Not applicable | Not applicable |
SERVICE | ALTER, CONTROL, SEND, TAKE OWNERSHIP, VIEW DEFINITION | CONTROL | DATABASE | ALTER ANY SERVICE, CONTROL, VIEW DEFINITION |
SYMMETRIC KEY | ALTER, CONTROL, REFERENCES, TAKE OWNERSHIP, VIEW DEFINITION | CONTROL | DATABASE | ALTER ANY SYMMETRIC KEY, CONTROL, REFERENCES, VIEW DEFINITION |
TYPE | CONTROL, EXECUTE, REFERENCES, TAKE OWNERSHIP, VIEW DEFINITION | CONTROL | SCHEMA | CONTROL, EXECUTE, REFERENCES, VIEW DEFINITION |
USER | ALTER, CONTROL, IMPERSONATE, VIEW DEFINITION | CONTROL | DATABASE | ALTER ANY USER, CONTROL, VIEW DEFINITION |
XML SCHEMA COLLECTION | ALTER, CONTROL, EXECUTE, REFERENCES, TAKE OWNERSHIP, VIEW DEFINITION | CONTROL | SCHEMA | ALTER, CONTROL, EXECUTE, REFERENCES, VIEW DEFINITION |
Statement permissions control administration actions, such as creating a database or adding objects to a database. Only members of the sysad min role and database owners can assign statement permissions. By default, normal logins are not granted statement permissions, and you must specifically grant these permissions to logins that are not administrators. For example, if a user needs to be able to create views in a database, you would assign permission to execute CREATE VIEW. Table 8-5 provides a summary of statement permissions that you can grant, deny, or revoke.
Table 8-5. Statement Permissions
Statement Permission | Description |
|---|---|
CREATE DATABASE | Determines if the login can create databases. The user must be in the master database or must be a member of the sysadmin server role. |
CREATE DEFAULT | Determines if the user can create a default value for a table column. |
CREATE FUNCTION | Determines if the user can create a user-defined function in the database. |
CREATE PROCEDURE | Determines if the user can create a stored procedure. |
CREATE RULE | Determines if the user can create a table column rule. |
CREATE TABLE | Determines if the user can create a table. |
CREATE VIEW | Determines if the user can create a view. |
BACKUP DATABASE | Determines if the user can back up the database. |
BACKUP LOG | Determines if the user can back up the transaction log. |
Only members of predefined system roles or database/database object owners have implied permissions. Implied permissions for a role cannot be changed. You make other accounts members of the role to give the accounts the related implied permissions. For example, members of the sysadmin server role can perform any activity in SQL Server. They can extend databases, kill processes, and so on. Any account you add to the sysadmin role can perform these tasks as well.
Database and database object owners also have implied permissions. These permissions allow them to perform all activities with either the database or the object they own, or with both. For example, a user who owns a table can view, add, change, and delete data. That user can also alter the table’s definition and control the table’s permissions.