SQL Server, remove all users' access right to a table object
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?
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.