Permissions
Every object in a Microsoft® SQL Server™ 2000 database has an owner, typically the user ID 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 dbcreator fixed server roles. Users cannot access an object or execute a statement unless authorized.
All Transact-SQL statements that a user issues are subject to the permissions the user has been granted. Members of the sysadmin fixed server role, members of the db_owner 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 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 make foreign key references to the primary key and unique columns of a table. Also used to allow SCHEMABINDING references from views and functions.
- 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.