By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,077 Members | 1,237 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,077 IT Pros & Developers. It's quick & easy.

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

P: n/a
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
Share this Question
Share on Google+
2 Replies


P: n/a
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

P: n/a
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.