469,626 Members | 883 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,626 developers. It's quick & easy.

Script to copy permissions for all objects given to a user or a role

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
respective databases?

Help appreciated
Jul 20 '05 #1
2 5189
gu*****@yahoo.com (gudia) wrote in message news:<21**************************@posting.google. com>...
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
respective databases?

Help appreciated


First of all, I would avoid granting permissions to users, since this
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:

select
case protecttype
when 205 then 'grant '
when 206 then 'revoke '
end +
case action
when 26 then ' references '
when 193 then ' select '
when 195 then ' insert '
when 196 then ' delete '
when 197 then ' update '
when 224 then ' execute '
end +
' on ' + object_name(id) + ' to TargetRole '
+ case when protecttype = 204 then ' with grant option' else '' end
from
sysprotects
where
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.

Simon
Jul 20 '05 #2
Simon:

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!
Jul 20 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

3 posts views Thread by comp_databases_ms-sqlserver | last post: by
1 post views Thread by LM | last post: by
4 posts views Thread by Arno R | last post: by
2 posts views Thread by Maximus | last post: by
2 posts views Thread by Anthony Smith | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.