Sql-server – SQL Server, remove all users’ access right to a table object

Securitysql-server-2008

As a clean up job, I am trying to remove all users' (including non public users) access right to a single table object (apart from superusers) and will subsequently grant new access rights. Is there a simple way to remove access rights to a table without using a cursor with system tables?

Best Answer

This code should work below:

declare 
    @user_name sysname,
    @deny_cmd nvarchar(500)

declare UserCursor cursor for
select name
from sys.database_principals
where type in ('U', 'S')
and name not in ('dbo', 'information_schema', 'sys')

open UserCursor

fetch next from UserCursor
into @user_name

while @@fetch_status = 0
begin
    set @deny_cmd = 
        'deny select, insert, update, delete on object::UberSecureTable
        to ' + @user_name

    exec (@deny_cmd)

    fetch next from UserCursor
    into @user_name
end

close UserCursor
deallocate UserCursor

Let me know if that does what you're looking for. Change UberSecureTable to your actual table name.

Related Solutions

Sql-server – Trying to better understand how SELECT rights are managed in a/the SQL Server db

You need to look at the Effective Permissions http://msdn.microsoft.com/en-us/library/ms178358.aspx

I've never understood why there isn't a version of fn_my_permissions http://msdn.microsoft.com/en-us/library/ms176097.aspx which simply takes a user.

To do that you have to EXECUTE AS the other user: http://sqlarea.blogspot.com/2008/04/fnmypermissions.html

SQL Server 2008 R2 – Grant Role in One Database Access to Tables in Another Database

Database roles are security principals that are wholly contained within their respective database and are not shared or visible to other databases. So any roles and users that are in database X have no knowledge of database Y. To accomplish your goal, you'll need to recreate the role in database Y and add all the appropriate users to this database and role. Fortunately, you can script out this process using the system views:

USE [Y];
CREATE ROLE foo;

--grant all perms on foo

--Use this generate sql to add all your users to the role
select 'CREATE USER '+quotename(u.name)+';' + char(10)+
       'EXEC sp_addrolemember ''foo'','''+u.name+''';'
from (select name,principal_id
        from x.sys.database_principals where type = 'R') r
    join x.sys.database_role_members rm 
        on (r.principal_id = rm.role_principal_id)
    join (select name,type_desc,principal_id
        from x.sys.database_principals where type != 'R') u 
        on (rm.member_principal_id = u.principal_id )
where r.name = 'foo'

You can then take this sort of script/process to a batch job to keep things synchronized, but you will need some sort of external process to keep these roles in sync.

One piece that might be confusing here is that you have logins and other server level principals that are connected to the database principals, but there is a clear distinction between these. I provide some explanation about the differences between these principals in this answer

Best Answer

Related Solutions

Sql-server – Trying to better understand how SELECT rights are managed in a/the SQL Server db

SQL Server 2008 R2 – Grant Role in One Database Access to Tables in Another Database

Related Question