Permissions

Every object in a Microsoft® SQL Server™ database has an owner, typically the user ID that was in effect for the connection that created the object. Other users cannot access that object until the owner authorizes their user ID to access the object.

Certain Transact-SQL statements are also limited to specific user IDs. For example, CREATE DATABASE is limited to members of the sysadmin and db_creator fixed server roles. Users cannot access an object or execute a statement unless they have been authorized to do so.

All Transact-SQL statements that a user issues are subject to the permissions the user has been given. Members of the sysadmin fixed server role, members of the dbowner fixed database role, and owners of database objects can grant, deny, or revoke permissions for a person or role. When using Transact-SQL, use the GRANT, DENY, and REVOKE statements to specify who can use which data-modification statements:

• GRANT gives permissions to either work with data or execute other Transact-SQL statements.
• DENY denies a permission and prevents the specified user, group, or role from inheriting the permission through group and role memberships.
• REVOKE removes previously granted or denied permissions.

The permissions that can be granted for objects are:

• SELECT

  Permits a user to issue SELECT statements against a table or view.

• INSERT

  Permits a user to issue INSERT statements against a table or view.

• UPDATE

  Permits a user to issue UPDATE statements against a table or view.

• DELETE

  Permits a user to issue DELETE statements against a table or view.

• REFERENCES

  Permits a user to insert a row in a table that has a foreign key referencing the table named in the GRANT, DENY, and REVOKE statement.

• EXECUTE

  Permits a user to issue EXECUTE statements against a stored procedure.

Permissions can also be granted to execute Transact-SQL statements that are usually limited to members of a specific role. For example, a member of the sysadmin fixed server role can grant CREATE DATABASE permissions to a user who usually could not create databases.

See Also