Owners and Permissions

Every object in Microsoft® SQL Server™ 2000 is owned by a user. The owner is identified by a database user identifier (ID). When an object is first created, the only user ID that can access the object is the user ID of the owner or creator. For any other user to access the object, the owner must grant permissions to that user. If the owner wants only specific users to access the object, the owner can grant permissions to those specific users.

For tables and views, the owner can grant INSERT, UPDATE, DELETE, SELECT, and REFERENCES permissions, or ALL permissions. A user must have INSERT, UPDATE, DELETE, or SELECT permissions on a table before they can specify it in INSERT, UPDATE, DELETE, or SELECT statements. The REFERENCES permission lets the owner of another table use columns in your table as the target of a REFERENCES FOREIGN KEY constraint from their table. The following example illustrates granting SELECT permissions to a group named Teachers and REFERENCES permissions to another development user:

GRANT SELECT ON MyTable TO Teachers
GRANT REFERENCES (PrimaryKeyCol) ON MyTable to DevUser1

The owner of a stored procedure can grant EXECUTE permissions for the stored procedure. If the owner of a base table wants to prevent users from accessing the table directly, they can grant permissions on views or stored procedures referencing the table, but not grant any permissions on the table itself. This is the foundation of the SQL Server mechanisms to ensure that users do not see data they are not authorized to access.

Users can also be granted statement permissions. Some statements, such as CREATE TABLE and CREATE VIEW, can only be executed by certain users (in this case, the dbo user). If the dbo wants another user to be able to create tables or views, they must grant the permission to execute these statements to that user.