Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 


 
     
Recent Posts
   
Popular Topics
Home   
Search
    Members    Calendar    Who's On

Home » SQL Server 2008 » SQL Server 2008 Administration » Add multiple users and give permissions to...
Add to briefcase

Add multiple users and give permissions to many databases Expand / Collapse
Rate Topic
Display Mode
Topic Options
Author
Message
PHXHoward
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: Today @ 12:05 AM
Points: 255, Visits: 717
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
Luis Cazares
Posted Thursday, August 23, 2012 11:58 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 4:16 PM
Points: 1,091, Visits: 2,205
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.
Please don't trust me, test the solutions I give you before using them.
Forum Etiquette: How to post data/code on a forum to get the best help
Post #1349279
PHXHoward
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: Today @ 12:05 AM
Points: 255, Visits: 717
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
Luis Cazares
Posted Thursday, August 23, 2012 12:13 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 4:16 PM
Points: 1,091, Visits: 2,205
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.
Please don't trust me, test the solutions I give you before using them.
Forum Etiquette: How to post data/code on a forum to get the best help
Post #1349287
PHXHoward
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: Today @ 12:05 AM
Points: 255, Visits: 717
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
Luis Cazares
Posted Thursday, August 23, 2012 12:37 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 4:16 PM
Points: 1,091, Visits: 2,205
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.
Please don't trust me, test the solutions I give you before using them.
Forum Etiquette: How to post data/code on a forum to get the best help
Post #1349299
PHXHoward
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: Today @ 12:05 AM
Points: 255, Visits: 717
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
RBarryYoung
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: Wednesday, June 12, 2013 11:17 AM
Points: 9,855, Visits: 9,376
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
belvdr
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, May 29, 2013 5:59 AM
Points: 8, Visits: 61
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
opc.three
Posted Thursday, August 30, 2012 2:55 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 1:07 PM
Points: 6,826, Visits: 11,951
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

Believe you can and you're halfway there. --Theodore Roosevelt

Everything Should Be Made as Simple as Possible, But Not Simpler --Albert Einstein

The significant problems we face cannot be solved at the same level of thinking we were at when we created them. --Albert Einstein

1 apple is not exactly 1/8 of 8 apples. Because there are no absolutely identical apples. --Giordy
Post #1352508
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse

 
 
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.