Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question

migrated from stackoverflow.com Jun 13 '12 at 17:25

This question came from our site for professional and enthusiast programmers.

    
are you wanting to revoke a single users rights to all tables or all users rights to a single table? –  Brian Jun 13 '12 at 17:03
1  
use the normal revoke command with the public option this revokes access to the table for all public users. –  Brian Jun 13 '12 at 17:08
    
@Brian Not all users are in the public role. Sorry will edit the question. –  NaN Jun 13 '12 at 17:18
add comment

1 Answer 1

up vote 2 down vote accepted

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.

share|improve this answer
    
Thanks @Shark. This works and does the job, but I was looking to see if there's an admin command that can avoid using cursor. As an analogy, one can delete rows, but there's also the truncate command. I still up vote your answer though. –  NaN Jun 14 '12 at 8:14
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.