Thursday, March 29, 2012

granting permissions on set 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.What I would probably do is create a role and call it something like abc_tables. Then generate the commands to grant the permissions on those tables with a query like:

select 'grant select, update, insert, delete on ' + name + ' to abc_tables'
from sysobjects
where type = 'U'
and name like 'abc%'

Run the resulting grant statements, then add the user to the role. The beauty of this is, if you have to grant the same permissions to another user, then you have most of the work already done.

No comments:

Post a Comment