Script to copy permissions for all objects given to a user or a role - 07-07-2004 , 05:38 PM
How would I, using a sql script, copy permissions assigned to a user
or a role in one or more databases to another user or a role in their
Re: Script to copy permissions for all objects given to a user or a role - 07-08-2004 , 03:01 AM
gudia97 (AT) yahoo (DOT) com (gudia) wrote in message news:<2130f7ff.0407071438.275b76f3 (AT) posting (DOT) google.com>...
becomes difficult to manage - if you always use roles, then things are
much easier. Even if you have only one user in a role today, at least
you won't have any extra work when you need to add a second one. Also,
you should keep a permissions script for each role, so you know which
permissions are correct, and you can run it for multiple roles.
To solve your issue right now, you could add one role to another, if
that's appropriate. If not, then you can reverse-engineer a
permissions script using a query like this:
when 205 then 'grant '
when 206 then 'revoke '
when 26 then ' references '
when 193 then ' select '
when 195 then ' insert '
when 196 then ' delete '
when 197 then ' update '
when 224 then ' execute '
' on ' + object_name(id) + ' to TargetRole '
+ case when protecttype = 204 then ' with grant option' else '' end
uid = user_id('SourceRole')
See sysprotects in Books Online for more information. Note that this
query doesn't handle SELECT or UPDATE permissions on explicit columns,
but only where the permissions are on the whole table. It also does
not handle statement permissions (CREATE TABLE etc.), but you can
easily write a similar query for that.
Re: Script to copy permissions for all objects given to a user or a role - 07-08-2004 , 08:26 AM
Thanks. This will help as a starting point. I do use roles as oppossed
to users for permissioning.
*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!