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

Copy permissions of a database role

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


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

P: n/a
Here ya go.. someone wrote a script for you.. thank you Google!

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

Jul 23 '05 #3

P: n/a
Thanks a lot Joshsackett. I am testing Andy Warren's script and post
here my result.

Jul 23 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.