473,320 Members | 1,922 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,320 software developers and data experts.

Copy permissions of a database role

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 database gets unnecessary access to objects by virtue
of being a member of public by default. I would like to fix this flaw
in the way the server is setup as below:
1. Setup a new database role called NewRole on all 50 databases and
copy permissions to NewRole from public.
2. Add all existing users to the new Role.
3. Remove all permissions from public.

Any suggestions on scripting this task are welcome.

Jul 23 '05 #1
3 9495
sp_msforeachdb 'USE ? if db_id(''?'') > 4
BEGIN
exec sp_addrole ''NewRole''
END'

The above script will add the role to each database. Copying
permissions is going to be a bit harder because you have to send direct
updates to the syspermissions table.. not impossible, but difficult.
Let me chew on it some more.

Jul 23 '05 #2
Here ya go.. someone wrote a script for you.. thank you Google!

http://www.databasejournal.com/featu...le.php/1478701

Jul 23 '05 #3
Thanks a lot Joshsackett. I am testing Andy Warren's script and post
here my result.

Jul 23 '05 #4

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
by: Jon Ley | last post by:
I am having a problem restricting write access to tables in my database. In my database I have a table called, for the sake of argument, 'TableX'. In my SQL Server Logins, I have set up a login...
2
by: gudia | last post by:
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
5
by: Ross Presser | last post by:
As our customers demand that we tighten our IT security in the company, I've been asked to prepare a report quarterly showing, for each user in Active directory, what his effective permissions are...
2
by: Matt Norwood | last post by:
I am in need of a utility (stored procedure, third party app, etc) that will help streamline the process of managing and auditing object permissions, users and roles on multiple sql server 2000...
4
by: ralphm1 | last post by:
Hello, I am running SQL Server 2000 standard in mixed mode security and have two problems. 1.) I created a database as sa and assigned a login as db_owner, however, the design view is grayed...
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: Xia Wei | last post by:
Hi group, I'm trying to use CAS in my project these days. And I find a problem, for example: static void Exec() {} Then the caller of this method should be a member of role "Xxx". If I...
9
by: Nemisis | last post by:
Hi everyone, hope your all looking forward to xmas. I am setting up a Sql2005 database on a Windows Server, running Windows Server 2003. The database is going to be accessed via users using an...
6
by: DotNetNewbie | last post by:
Hello, in my web application, I have to create permissions for each user. So what I am doing is that for each role (using sqlmembership in .net) I am creating a column in the database to hold a...
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: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you

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.