How to revoke all objects except one object for a

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Administration

How to revoke all objects except one object for a

Author

Topic

Ravikumarc
Yak Posting Veteran

51 Posts

Posted - 2009-09-08 : 18:49:16

Dear All,

Im new bee to Sql DBA,can you pls let me knw "How to revoke all objects except one object for a user who has select permission"

User named Dr has 150 tables,here i have to revoke 149 tables select permission except one table....

Please advice on this issue

Thanks & Regards
Ravi

robvolk
Most Valuable Yak

15732 Posts

Posted - 2009-09-08 : 19:10:24

exec sp_msforeachtable 'revoke select on ? to DR'
grant select on myTable to DR

Ravikumarc
Yak Posting Veteran

51 Posts

Posted - 2009-09-09 : 12:54:24

quote:
Originally posted by robvolk

exec sp_msforeachtable 'revoke select on ? to DR'
grant select on myTable to DR

Hi Rob,

Its working fine,Thanks a lot :-)

Can u pls advice me how to revoke Views,stored procedure..

Thanks & Regards
Ravi

robvolk
Most Valuable Yak

15732 Posts

Posted - 2009-09-09 : 13:39:27

SELECT 'REVOKE SELECT ON ' + quotename(table_schema) + '.' + quotename(table_name) + ' TO DR '
FROM INFORMATION_SCHEMA.VIEWS
SELECT 'REVOKE EXECUTE ON ' + quotename(routine_schema) + '.' + quotename(routine_name) + ' TO DR '
FROM INFORMATION_SCHEMA.ROUTINES

Copy those results and paste them into a new query window and run them. You should review the output to be sure you include only the views and procedures you need.

Ravikumarc
Yak Posting Veteran

51 Posts

Posted - 2009-09-09 : 15:13:56

quote:
Originally posted by robvolk

SELECT 'REVOKE SELECT ON ' + quotename(table_schema) + '.' + quotename(table_name) + ' TO DR '
FROM INFORMATION_SCHEMA.VIEWS
SELECT 'REVOKE EXECUTE ON ' + quotename(routine_schema) + '.' + quotename(routine_name) + ' TO DR '
FROM INFORMATION_SCHEMA.ROUTINES

Copy those results and paste them into a new query window and run them. You should review the output to be sure you include only the views and procedures you need.

Dear Rob,

Great !!!! Ya i got what i want...
im a beginner for SQL DBA,So i kindly request u to mentor me..

Thanks a lot Rob..

Regards
Ravi

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources