Database Roles

Question

Post reply

Database Roles

PFlorenzano

SSCrazy

Points: 2120
More actions
January 14, 2004 at 11:52 am

#65762

Hello everyone, Is there a way you can REVOKE permissions on a user defined role? like 'SELECT','UPDATE',DELETE','INSERT' instead of on the user level? Thanks!, Pete
Peter M. Florenzano

Database Administrator

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Allen Cui-55137 SSC Guru Points: 51650 More actions · Answer 1

revoke select, update, delete, insert on yourtable from user_defined_role

PFlorenzano SSCrazy Points: 2120 More actions · Answer 2

Hi Allen and thank you for the reply. I'm aware of this command on a single table, but is there a way to execute this command with all of my tables in my database, without me going into SQL Server Enterprise Manager to check the boxes in the roles section? I have over 1500 tables. Thanks again, Pete

Peter M. Florenzano

Database Administrator

Allen Cui-55137 SSC Guru Points: 51650 More actions · Answer 3

select 'revoke select, update, delete, insert on ' + name + ' from user_defined_role ' from sysobjects where xtype = 'U'

It will generate statements to all your user tables and cut and paste the statements to QA and run them.

PFlorenzano SSCrazy Points: 2120 More actions · Answer 4

Thank you Allen, I will let you know how it worked!, Pete

Peter M. Florenzano

Database Administrator

John McC Hall of Fame Points: 3410 More actions · Answer 5

You could also try using the sp_MSforeachtable sproc. It might be a bit quicker.

EXEC sp_MSforeachtable @command1 = 'revoke select, update, delete, insert on ? from user_defined_role'

-------------------------------------------------------------------------
Normal chaos will be resumed as soon as possible. :crazy: