# Data Control Language ## SQL Server: Grant/Revoke Privileges Learn how to **grant and revoke privileges** in SQL Server (Transact-SQL) with syntax and examples. ### Description You can GRANT and REVOKE privileges on various database objects in SQL Server. We'll look at how to grant and revoke privileges on tables in SQL Server. ### Grant Privileges on Table You can grant users various privileges to tables. These permissions can be any combination of SELECT, INSERT, UPDATE, DELETE, REFERENCES, ALTER, or ALL. #### Syntax The syntax for granting privileges on a table in SQL Server is: ```sql GRANT privileges ON object TO user; ``` privileges The privileges to assign. It can be any of the following values: | Privilege | Description | | ---------- | ----------------------------------------------------------------------------------------------------------------------------------------------------- | | SELECT | Ability to perform SELECT statements on the table. | | INSERT | Ability to perform INSERT statements on the table. | | UPDATE | Ability to perform UPDATE statements on the table. | | DELETE | Ability to perform DELETE statements on the table. | | REFERENCES | Ability to create a constraint that refers to the table. | | ALTER | Ability to perform ALTER TABLE statements to change the table definition. | | ALL | ALL does not grant all permissions for the table. Rather, it grants the ANSI-92 permissions which are SELECT, INSERT, UPDATE, DELETE, and REFERENCES. | objectThe name of the database object that you are granting permissions for. In the case of granting privileges on a table, this would be the table name.userThe name of the user that will be granted these privileges. #### Example Let's look at some examples of how to grant privileges on tables in SQL Server. For example, if you wanted to grant SELECT, INSERT, UPDATE, and DELETE privileges on a table called *employees* to a user name *smithj*, you would run the following GRANT statement: ```sql GRANT SELECT, INSERT, UPDATE, DELETE ON employees TO smithj; ``` You can also use the ALL keyword to indicate that you wish to grant the ANSI-92 permissions (ie: SELECT, INSERT, UPDATE, DELETE, and REFERENCES) to a user named *smithj*. For example: ```sql GRANT ALL ON employees TO smithj; ``` If you wanted to grant only SELECT access on the *employees* table to all users, you could grant the privileges to the public role. For example: ```sql GRANT SELECT ON employees TO public; ``` ### Revoke Privileges on Table Once you have granted privileges, you may need to revoke some or all of these privileges. To do this, you can run a revoke command. You can revoke any combination of SELECT, INSERT, UPDATE, DELETE, REFERENCES, ALTER, or ALL. #### Syntax The syntax for revoking privileges on a table in SQL Server is: ```sql REVOKE privileges ON object FROM user; ``` privileges It is the privileges to assign. It can be any of the following values: | Privilege | Description | | ---------- | ------------------------------------------------------------------------------------------------------------------------------------------------------- | | SELECT | Ability to perform SELECT statements on the table. | | INSERT | Ability to perform INSERT statements on the table. | | UPDATE | Ability to perform UPDATE statements on the table. | | DELETE | Ability to perform DELETE statements on the table. | | REFERENCES | Ability to create a constraint that refers to the table. | | ALTER | Ability to perform ALTER TABLE statements to change the table definition. | | ALL | ALL does not revoke all permissions for the table. Rather, it revokes the ANSI-92 permissions which are SELECT, INSERT, UPDATE, DELETE, and REFERENCES. | objectThe name of the database object that you are revoking privileges for. In the case of revoking privileges on a table, this would be the table name.userThe name of the user that will have these privileges revoked. #### Example Let's look at some examples of how to revoke privileges on tables in SQL Server. For example, if you wanted to revoke DELETE privileges on a table called *employees* from a user named *anderson*, you would run the following REVOKE statement: ```sql REVOKE DELETE ON employees FROM anderson; ``` If you wanted to revoke ALL ANSI-92 permissions (ie: SELECT, INSERT, UPDATE, DELETE, and REFERENCES) on a table for a user named *anderson*, you could use the ALL keyword as follows: ```sql REVOKE ALL ON employees FROM anderson; ``` If you had granted SELECT privileges to the public role (ie: all users) on the *employees* table and you wanted to revoke these privileges, you could run the following REVOKE statement: ```sql REVOKE SELECT ON employees FROM public; ``` --- # Agent Instructions: Querying This Documentation If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question. Perform an HTTP GET request on the current page URL with the `ask` query parameter: ``` GET https://gyansetu-sql.gitbook.io/sql-programming/data-control-language.md?ask= ``` The question should be specific, self-contained, and written in natural language. The response will contain a direct answer to the question and relevant excerpts and sources from the documentation. Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.