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.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2005-08-12 : 08:12:19
|
| Anthony writes "I am trying to write a procedure to revoke access to mutiple tabels in MS SQL, namely 2000 odd tables. However i have appeared to hit a brick wall with the grant and revoke commands.I am thinking along the lines of walking over every table getting its name, then using it in a revoke statement, since the revoke statement wont allow multiple tables.After the revoke, i want to give people select rights on specific tables, but yet again the grant function wont accept multiple table names. So i will have to put a predefined array into the procedure.The problem that i am having is that my DB is deleted, and re-generated by a script nightly, thus everyday the assigned rights are lost, and the public role gains full access." |
|
|
Thrasymachus
Constraint Violating Yak Guru
483 Posts |
Posted - 2005-08-12 : 09:00:40
|
| "The problem that i am having is that my DB is deleted, and re-generated by a script nightly, thus everyday the assigned rights are lost, and the public role gains full access."I can't fathom why this would ever be necessary unless this is a subscriber in snaphot replication.Have you thought about querying sysobjects for your tables and looping through that?====================================================Regards,Sean RoussyThank you, drive through |
 |
|
|
elwoos
Master Smack Fu Yak Hacker
2052 Posts |
Posted - 2005-08-12 : 09:02:03
|
| Enterprise Mangler can script object permissions if they are already set how you want them. You could also use Sysobjects to get all the table names.steveAlright Brain, you don't like me, and I don't like you. But lets just do this, and I can get back to killing you with beer. |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2005-08-12 : 09:08:25
|
| >>So i will have to put a predefined array into the procedure.You need something like thisselect 'Grant Select on '+table_name+' to '+user from Information_Schema.tableswhere table_type='Base table'MadhivananFailing to plan is Planning to fail |
|
|
|
|
|
|
|
|
Revoking access on multiple tables