Granting rights to a user for all DB's

Question

Post reply

Granting rights to a user for all DB's

espanolanthony

SSCarpal Tunnel

Points: 4204
More actions
November 23, 2010 at 8:42 am

#147728

I have SQL Server 2005.
I have a new windows user and i need to give him access to all the databases in SQL server. I know the process of giveing access to the databases. But using SSMS how to give access to all the databases in some easy way. I have almost 45 databases in the server and mapping him one by one to all the databases is very time consuming. I also need to give him same read/write permission to all the DB's. Any easy way to do that from SSMS.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

espanolanthony SSCarpal Tunnel Points: 4204 More actions · Answer 1

espanolanthony

SSCarpal Tunnel

Points: 4204

November 23, 2010 at 9:05 am

#1254528

any word on this

GSquared SSC Guru Points: 260824 More actions · Answer 2

Check out sp_MSForEachDB and see if that will do what you need.

Or create a group that has read/write access to all databases, and add him to that group instead of mapping him individually. That will be better, because future logins can then be managed through that group too. Also means if someone needs to lose access (leaves the company, whatever), you just remove them from that group, instead of from every single database.

Also makes it easy if you add that group to the model databases, and then new databases will already have the group set up.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

GSquared SSC Guru Points: 260824 More actions · Answer 3

espanolanthony (11/23/2010)
any word on this

Please keep in mind that this isn't a forum for paid, high-speed support. It's volunteers who help when/if they can.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

espanolanthony SSCarpal Tunnel Points: 4204 More actions · Answer 4

sorry about that, but can you please show me the steps or details. I don't know anything about that.

espanolanthony SSCarpal Tunnel Points: 4204 More actions · Answer 5

can someone show me how to

create a group that has read/write access to all databases, and add him to that group instead of mapping him individually.

homebrew01 SSC Guru Points: 55548 More actions · Answer 6

A bit impatient ? (You could have done it manually by now)

Create a Windows secirity group through AD, then put the user(s) in that group, then give that group specific rights.

As for granting the permissions, get one set up with the GUI, then script it to a new window, and then use the sp_MSForEachDB suggestion

GSquared SSC Guru Points: 260824 More actions · Answer 7

You create a group in Active Directory, or have your systems people do that for you if you can't do that yourself.

Then you create a login for the AD group. Will look something like this:

CREATE LOGIN [MyADDomain\DataUserGroup] FROM WINDOWS WITH DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english];

Then you create a script to add that group to every database:

select 'use [' + name + ']; CREATE USER [DataUserGroup] FOR LOGIN [MyADDomain\DataUserGroup] WITH DEFAULT_SCHEMA=[dbo];'

from sys.databases;

Copy-and-paste the results from that into a connection in SSMS and run it, or assign it to a variable and execute it. Either one works.

Then do the same with the rights:

EXEC sp_addrolemember N'db_datareader', N'DataUserGroup'

Again, add that into a Select statement that gets each database name into a "USE" statement, and execute manually or dynamically.

Then, all you have to do, is manage the AD group. Add or remove people whenever you need to.

Make sure the login is created in "model", so new databases get it. Might not need to be in "master" or "msdb", will probably need to be in "tempdb" (it it's in "model", it'll get added back into "tempdb" whenever that's re-created by the SQL service starting up).

It takes a few minutes work per server, once the AD group is set up, but it saves you a lot of future work.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon