granting permissions on group of tables
-
I have 500 tables in my Db.In those some tables name starts with abc (abc_emp,abc_dept) and rest of the tables name start with xyz(xyz_emp,xyz_transactions).I wanted to give select,insert,update and delete permissions for an user on the tables which starts with abc.
How can i do that in a much easier and sophisticated way.
Thanks.
-
This should work for you. If you have a lot of users, you can create a cursor like the one I have here for the table name. Otherwise, just plug the user name into the @user variable and execute. As it is written, it will output the grant statements in the messages window in query analyzer. You would need to copy and paste them into a new query window and execute. OR, you could simply uncomment the exec statement and it'll do it for you.
Hope this helps,
Steve
declare @table varchar(128), @user varchar(128), @sql varchar(1000)
declare table_cur cursor for
select name from sysobjects where xtype = 'u' and name like 'abc%' order by name
set @user = 'sahana'
open table_cur
fetch next from table_cur into @table
IF @@fetch_status = -1
BEGIN
CLOSE table_cur
DEALLOCATE table_cur
END
ELSE
BEGIN
WHILE @@fetch_status <> -1
BEGIN
set @sql = 'GRANT SELECT, INSERT, UPDATE, DELETE ON ' + @table + ' TO ' + @user
print @sql
-- exec (@sql)
fetch next from table_cur into @table
end -- while
end -- if
close table_cur
deallocate table_cur
-
While the script above will do precisely what you asked it would be much better to create a database role and grant the permissions to that. Simply create the role and then use the script above to set the permssions. Then you simply assign the user to the role and they get all the permissions along with it.
This way the next time you have to add a user it is a simple matter of assigning the role they need rather than having to go through all 500 tables and who knows how many SPs to set their permissions.
Gary Johnson
Microsoft Natural Language Group
DBA, Sr. DB EngineerThis posting is provided "AS IS" with no warranties, and confers no rights. The opinions expressed in this post are my own and may not reflect that of my employer.
-
Another way is to run the following command and paste the output to query analyzer window and execute.
Hope it helps
select 'Grant select, insert, update , delete on ' + name + ' to aaaa; '
from sysobjects where type = 'U' and
name like 'abc%'
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply