CODEWITHSUNDEEP
sql-serversql-server-2012
Chad
Chad

Reputation: 24699

Remove all user's rights to all tables in one statement

I am trying to remove all of the following permissions on a table from a user:

select,update,delete,insert,execute,references,view definition

I tried this:

REVOKE select,update,delete,insert,execute,references,view definition on schema::DBO to [user]

and this:

REVOKE select,update,delete,insert,execute,references,view definition to [user]

I don't really underdstand the difference. I would assume that the first applies only to objects in the dbo schema and the latter refers to objects in all schema.

In any event, if I select the table in SSMS and look at the Properties, select "Permissions", and then select the user in the top grid, I see the permissions that the user has for the table. SELECT and VIEW DEFINITION rows each appear twice in the bottom grid, once with a GRANTOR of "DBO" and once without.

Unfortunately, after I run both of the above statements, the user still has rights where the rows for GRANTOR is dbo. The following REVOKE statements will clear these remaining rights:

REVOKE VIEW DEFINITION ON [dbo].[TableNAME] TO [USER] AS [dbo]
REVOKE SELECT ON [dbo].[TableNAME] TO [USER] AS [dbo]

But I'd like to remove these rights too, without having to do it individually for all of his tables.

I assume that it is possible to remove all of the user's access for all tables. How do I do this in a single statement?

I was trying to avoid reading meta data and dynamic sql, but I tried this:

DECLARE @GRANTOR        varchar(255)
DECLARE @GRANTEE        varchar(255)
DECLARE @TABLE_CATALOG  varchar(255)
DECLARE @TABLE_SCHEMA   varchar(255)
DECLARE @TABLE_NAME     varchar(255)
DECLARE @PRIVILEGE_TYPE     varchar(255)
DECLARE @SQL varchar(255)

DECLARE MyCursor CURSOR FOR

SELECT
    GRANTOR
   ,GRANTEE
   ,TABLE_CATALOG
   ,TABLE_SCHEMA
   ,TABLE_NAME
   ,PRIVILEGE_TYPE
FROM
    INFORMATION_SCHEMA.TABLE_PRIVILEGES

OPEN MyCursor

FETCH NEXT FROM MyCursor INTO     
    @GRANTOR
   ,@GRANTEE
   ,@TABLE_CATALOG
   ,@TABLE_SCHEMA
   ,@TABLE_NAME
   ,@PRIVILEGE_TYPE;

WHILE @@FETCH_STATUS = 0
BEGIN

    SET @SQL = 'REVOKE ' + @PRIVILEGE_TYPE + ' ON [' + @TABLE_SCHEMA + '].[' + @TABLE_NAME + '] TO [' + @GRANTEE + '] AS [' + @GRANTOR + ']';

            PRINT (@SQL)
    EXECUTE (@SQL)

    FETCH NEXT FROM MyCursor INTO
      @GRANTOR
    , @GRANTEE
    , @TABLE_CATALOG
    , @TABLE_SCHEMA
    , @TABLE_NAME
    , @PRIVILEGE_TYPE;


END


CLOSE MyCursor
DEALLOCATE MyCursor

I ran it with no errors and it left many SELECT and VIEW DEFINITION rights. It looks like the query of TABLE_PRIVILEGES did not return some privileges.

Upvotes: 2

Views: 3329

Answers (2)

tinker
tinker

Reputation: 652

The workaround I used,

  1. Script out the login+user.
  2. Delete login+user.
  3. Recreate it with only the permissions you want.

Works well enough, no need to mess with any complicated scripts.

It's a pity we can't do this easily from the GUI itself.

Upvotes: 1

Mohamed Azizi
Mohamed Azizi

Reputation: 162

This should do the work for you:

`deny SELECT, INSERT, DELETE, UPDATE on SCHEMA::SchemaName to user1,user2,user3

Hope it's what you need :)

Upvotes: 0

Related Questions