The following table summarizes the SQL Server permissions system. The type of user listed as the one the statement defaults to is the lowest user level to which the permission is automatically granted. This user can grant the permission to other users or revoke it from other users, if it is transferable.
Defaults to |
Can be granted / revoked |
|||||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
Statement |
System admin | DB owner | Table owner | Public |
Yes |
No |
N/A |
|||||||||||
| ALTER DATABASE | – | X | – | – | (1) | – | – | |||||||||||
| ALTER TABLE | – | – | X | – | – | X | – | |||||||||||
| BEGIN TRANSACTION | – | – | – | X | – | – | X | |||||||||||
| CHECKPOINT | – | X | – | – | – | X | – | |||||||||||
| COMMIT TRANSACTION | – | – | – | X | – | – | X | |||||||||||
| CREATE DATABASE | X | – | – | – | X | – | – | |||||||||||
| CREATE DEFAULT | – | X | – | – | X | – | – | |||||||||||
| CREATE INDEX | – | – | X | – | – | X | – | |||||||||||
| CREATE PROCEDURE (2) | – | X | – | – | X | – | – | |||||||||||
| CREATE RULE | – | X | – | – | X | – | – | |||||||||||
| CREATE TABLE | – | X | – | (3) | X (2) | – | – | |||||||||||
| CREATE TRIGGER | – | – | X | – | – | X | – | |||||||||||
| CREATE VIEW | – | X | – | – | X | – | – | |||||||||||
| DBCC | – | X | – | – | – | X | – | |||||||||||
| DELETE | – | – | X (4) | – | X | – | – | |||||||||||
| DISK INIT | X | – | – | – | – | X | – | |||||||||||
| DISK MIRROR | X | – | – | – | – | X | – | |||||||||||
| DISK REFIT | X | – | – | – | – | X | – | |||||||||||
| DISK REINIT | X | – | – | – | – | X | – | |||||||||||
| DISK REMIRROR | X | – | – | – | – | X | – | |||||||||||
| DISK UNMIRROR | X | – | – | – | – | X | – | |||||||||||
| DROP any object (5) | – | – | – | – | – | X | – | |||||||||||
| DUMP DATABASE | – | X | – | – | X | – | – | |||||||||||
| DUMP TRANSACTION | – | X | – | – | X | – | – | |||||||||||
| EXECUTE (6) | – | – | – | – | X | – | – | |||||||||||
| GRANT | – | X | – | – | – | X | – | |||||||||||
| GRANT on object (5) | – | – | – | – | – | X | – | |||||||||||
| INSERT | – | – | X (4) | – | X | – | – | |||||||||||
| KILL | X | – | – | – | – | X | – | |||||||||||
| LOAD DATABASE | – | X | – | – | – | X | – | |||||||||||
| LOAD TRANSACTION | – | X | – | – | – | X | – | |||||||||||
| – | – | – | X | – | – | X | ||||||||||||
| RAISERROR | – | – | – | X | – | – | X | |||||||||||
| READTEXT | – | – | X | – | (7) | – | – | |||||||||||
| RECONFIGURE | X | – | – | – | – | X | – | |||||||||||
| REFERENCE | – | – | X | – | X | – | – | |||||||||||
| REVOKE | – | X | – | – | – | X | – | |||||||||||
| REVOKE on object (5) | – | – | – | – | – | X | – | |||||||||||
| ROLLBACK TRANSACTION | – | – | – | X | – | – | X | |||||||||||
| SAVE TRANSACTION | – | – | – | X | – | – | X | |||||||||||
| SELECT | – | – | X (4) | – | X | – | – | |||||||||||
| SET | – | – | – | X | – | – | X | |||||||||||
| SETUSER | – | X | – | – | – | X | – | |||||||||||
| SHUTDOWN | X | – | – | – | – | X | – | |||||||||||
| TRUNCATE TABLE | – | – | X | – | – | X | – | |||||||||||
| UPDATE | – | – | X (4) | – | X | – | – | |||||||||||
| UPDATE STATISTICS | – | – | X | – | – | X | – | |||||||||||
| UPDATETEXT | – | – | X | – | – | – | – | |||||||||||
| WRITETEXT | – | – | X | – | (8) | – | – | |||||||||||
| 1) Transferred with CREATE DATABASE permission | ||||||||||||||||||
| (2) Permissions to create temporary procedures are granted by default. A user can create and execute temporary procedures in a database even if not explicitly granted CREATE PROCEDURE permission. | ||||||||||||||||||
| (3) Members of public can create temporary tables; no permission required | ||||||||||||||||||
| (4) If a view, permission defaults to view owner | ||||||||||||||||||
| (5) Defaults to object owner | ||||||||||||||||||
| (6) Defaults to stored-procedure owner | ||||||||||||||||||
| (7) Transferred with SELECT permission | ||||||||||||||||||
| (8) Transferred with UPDATE permission | ||||||||||||||||||