473,231 Members | 2,038 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,231 software developers and data experts.

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 5401
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
by: comp_databases_ms-sqlserver | last post by:
Brief description of the problem: My production server has about 50 databases and various permissions are granted to public role on all these databases. Because of this any new user added to any...
1
by: LM | last post by:
Hi, I have 1 developement Database and 3 databases for 3 different countries. They are all located at the same server. I have the same tables and the same roles in each database. I want to...
4
by: Arno R | last post by:
Hi all, I have a situation where I (have to) develop a database together with some other person. Let's say we have develop1.mdb and develop2.mdb We need to work on different 'pieces' of the app....
2
by: Maximus | last post by:
Hi, Does anybody know how I can programatically set modify permissions on a folder for the aspnet user. Any help will be appreciated. Thanks.
3
by: vj | last post by:
I'm building a large infrastructure with about 30 servers (all running linux). I allow my end users to write scripts which then get broken down in smaller parts and run across the 30 servers. The...
2
by: Mark D Powell | last post by:
I had thought that I had posted this yesterday but since I cannot find it here we go again. I have searched the archives but either the code I want is not posted or I missed it. I have a user...
3
by: Rahul B | last post by:
Hi, I have a user UCLDEV1 which is a part of staff and a group(db2schemagrp1) to which i have not given any permissions. The authorizations of that user are shown as db2 =get authorizations...
2
by: Anthony Smith | last post by:
I have a user object that is set when a user logs in. There are also permissions that I get about the user from a web service. Currently I take the results from those web services and store them as...
1
by: Zabivb | last post by:
how to add two numbers in html using java script, no's should be user input
0
by: VivesProcSPL | last post by:
Obviously, one of the original purposes of SQL is to make data query processing easy. The language uses many English-like terms and syntax in an effort to make it easy to learn, particularly for...
0
by: jianzs | last post by:
Introduction Cloud-native applications are conventionally identified as those designed and nurtured on cloud infrastructure. Such applications, rooted in cloud technologies, skillfully benefit from...
0
by: abbasky | last post by:
### Vandf component communication method one: data sharing ​ Vandf components can achieve data exchange through data sharing, state sharing, events, and other methods. Vandf's data exchange method...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
0
by: stefan129 | last post by:
Hey forum members, I'm exploring options for SSL certificates for multiple domains. Has anyone had experience with multi-domain SSL certificates? Any recommendations on reliable providers or specific...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: Aftab Ahmad | last post by:
So, I have written a code for a cmd called "Send WhatsApp Message" to open and send WhatsApp messaage. The code is given below. Dim IE As Object Set IE =...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.