x

Permission script

Does anyone have a script that will a NT group database server with read only access to the databases on the server?

more ▼

asked Nov 10, 2009 at 05:49 PM in Default

Wale Akanni gravatar image

Wale Akanni
1 1 1 1

I'm not sure I understand your question.
Nov 10, 2009 at 07:36 PM Raj More
Thx for your response Raj, I think I have figured it out.
Nov 12, 2009 at 11:22 AM Wale Akanni
(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

If I understand right, you want to grant a group read permissions on every database on the server?

If that is the case, you can use something like:

exec sp_msforeachdb '
print ''Processing ?'';
use [?]
if not exists (select * from sysusers where name = ''BUILTIN\Users'' )
    CREATE User [BUILTIN\Users] For Login [BUILTIN\Users];

EXEC sp_addrolemember N''db_datareader'', N''BUILTIN\Users'';
'

Of course, replacing Builtin\User with your group. There are a couple of things to be aware of though:

  1. This will give read permission to every database, including master and msdb. This may reveal more than you want to if you are not careful with it.

  2. There may be problems if you have "phantom users". This does not happen often, but may occur if you do something like restoring a backup from a different server. Make sure you remove any "phantom users" before running a script like this.

  3. Remember that any permissions that group had before will remain intact, so if you really want them to only have read permissions, then you will need to use something separate to remove other permissions.

more ▼

answered Nov 10, 2009 at 09:24 PM

TimothyAWiseman gravatar image

TimothyAWiseman
15.5k 20 23 32

+1 for psychic abilities :)
Nov 10, 2009 at 09:27 PM Matt Whitfield ♦♦
Thank you Timothy, this helps a lot.
Nov 12, 2009 at 11:19 AM Wale Akanni
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x87

asked: Nov 10, 2009 at 05:49 PM

Seen: 1104 times

Last Updated: Nov 10, 2009 at 05:49 PM