Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Add multiple users and give permissions to many databases Expand / Collapse
Author
Message
Posted Thursday, August 23, 2012 9:51 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 12:53 PM
Points: 298, Visits: 909
Hi everyone.

I have a list of logins that I need to add to SQL Server 2008.

We need to give each of these domain accounts db_owner permissions to the user databases. Aside from right clicking and then putting check boxes in all the user databases for each user, is there a scripted way that I can add the permissions to all databases except Master, Model, MSDB, Tempdb?

Thanks for reading.

Howard
Post #1349223
Posted Thursday, August 23, 2012 11:58 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 6:25 PM
Points: 3,333, Visits: 7,193
Have you tried making a server role and adding the logins to that server role?
Or you could make a script to create the logins and give permissions.

EDIT: Forget this post.



Luis C.
I am a great believer in luck, and I find the harder I work the more I have of it. Stephen Leacock

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1349279
Posted Thursday, August 23, 2012 12:01 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 12:53 PM
Points: 298, Visits: 909
Thanks for responding. Is there a server role to give db_owner permissions to a user that excludes all of the additional access of sysadmin?
Post #1349281
Posted Thursday, August 23, 2012 12:13 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 6:25 PM
Points: 3,333, Visits: 7,193
I'm sorry, I got confused.
If you're using Windows Authentication, you should create a Windows group and then create a Login for that group.
That way, you can manage the permissions as a group and you just have to do the job once.



Luis C.
I am a great believer in luck, and I find the harder I work the more I have of it. Stephen Leacock

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1349287
Posted Thursday, August 23, 2012 12:25 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 12:53 PM
Points: 298, Visits: 909
Creating a group is a great idea. I'll do that for the new server that is being built. For this old server, this is a one time thing. I made this and it seems to almost do what I'm looking for.


EXEC sp_MSForEachDB 'IF ''[?]'' NOT IN (''master'', ''model'', ''msdb'', ''tempdb'')
exec sp_addrolemember ''db_owner'', [domain\login]'

Unfortunately it skips databases that don't have a check box by them.
Post #1349292
Posted Thursday, August 23, 2012 12:37 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 6:25 PM
Points: 3,333, Visits: 7,193
Administration is not my forte, so you shouldn't take my advice as the only truth.
But you can use something like this.

declare @cmd1 varchar(500),
@cmd2 varchar(500)
set @cmd1 =
'use ?; if ''?'' NOT IN( ''tempdb'', ''Master'', ''Model'', ''msdb'') print ''CREATE USER MyGroup FOR LOGIN Mydomain\MyGroup'''
set @cmd2 =
'use ?; if ''?'' NOT IN( ''tempdb'', ''Master'', ''Model'', ''msdb'') print ''EXEC sp_addrolemember ''''db_owner'''', ''''MyGroup'''''''
exec sp_MSforeachdb @command1=@cmd1, @command2=@cmd2


This code won't do anything, it will just print a script. You could modify it to make it work, but you should test a part of it first.



Luis C.
I am a great believer in luck, and I find the harder I work the more I have of it. Stephen Leacock

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1349299
Posted Thursday, August 23, 2012 12:54 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 12:53 PM
Points: 298, Visits: 909
Thanks for the example.

I have made a script to do what I believe you indicated.


DECLARE @cmd1 varchar(500), @cmd2 varchar(500), @cmd3 varchar(500)

SET @cmd1 = 'print ''[?]'''
SET @cmd2 = 'IF ''[?]'' NOT IN (''master'', ''model'', ''msdb'', ''tempdb'') alter authorization on database::[?] to [domain/login]'
SET @cmd3 = 'IF ''[?]'' NOT IN (''master'', ''model'', ''msdb'', ''tempdb'') exec sp_addrolemember ''db_owner'', [domain/login]'
EXEC sp_MSForEachDB @command1=@cmd1, @command2=@cmd2 , @command3=@cmd3

cmd2 puts the checkbox next to the database in the login properties screen.

Post #1349310
Posted Thursday, August 23, 2012 1:13 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Thursday, June 5, 2014 10:54 AM
Points: 9,902, Visits: 9,480
FYI, I think that there's server roles in SQL 2012. (I haven't tried them yet, though).

-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
Post #1349324
Posted Saturday, August 25, 2012 6:37 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, June 11, 2014 12:11 PM
Points: 9, Visits: 70
PHXHoward (8/23/2012)
Thanks for the example.

I have made a script to do what I believe you indicated.


DECLARE @cmd1 varchar(500), @cmd2 varchar(500), @cmd3 varchar(500)

SET @cmd1 = 'print ''[?]'''
SET @cmd2 = 'IF ''[?]'' NOT IN (''master'', ''model'', ''msdb'', ''tempdb'') alter authorization on database::[?] to [domain/login]'
SET @cmd3 = 'IF ''[?]'' NOT IN (''master'', ''model'', ''msdb'', ''tempdb'') exec sp_addrolemember ''db_owner'', [domain/login]'
EXEC sp_MSForEachDB @command1=@cmd1, @command2=@cmd2 , @command3=@cmd3

cmd2 puts the checkbox next to the database in the login properties screen.



ALTER AUTHORIZATION is changing the ownership of the databases in question. You want to create a user in each database for each login and assign db_owner to each user.
Post #1350036
Posted Thursday, August 30, 2012 2:55 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 9:57 AM
Points: 7,080, Visits: 12,571
RBarryYoung (8/23/2012)
FYI, I think that there's server roles in SQL 2012. (I haven't tried them yet, though).

I demoed them a while back and unless I missed something the new Server roles in SQL 2012 are meant to help us group server-level permissions only, nothing at the database level, unfortunately.


__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Post #1352508
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse