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
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, December 17, 2014 3:10 PM
Points: 306, Visits: 967
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


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 7:08 PM
Points: 4,046, Visits: 9,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.
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

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
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, December 17, 2014 3:10 PM
Points: 306, Visits: 967
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


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 7:08 PM
Points: 4,046, Visits: 9,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.
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

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
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, December 17, 2014 3:10 PM
Points: 306, Visits: 967
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


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 7:08 PM
Points: 4,046, Visits: 9,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.
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

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
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, December 17, 2014 3:10 PM
Points: 306, Visits: 967
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, December 4, 2014 7:52 AM
Points: 9,294, Visits: 9,495
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: Thursday, December 18, 2014 8:51 PM
Points: 7,140, Visits: 12,763
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