August 23, 2012 at 9:51 am
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
August 23, 2012 at 11:58 am
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.
August 23, 2012 at 12:01 pm
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?
August 23, 2012 at 12:13 pm
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.
August 23, 2012 at 12:25 pm
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.
August 23, 2012 at 12:37 pm
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.
August 23, 2012 at 12:54 pm
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.
August 23, 2012 at 1:13 pm
FYI, I think that there's server roles in SQL 2012. (I haven't tried them yet, though).
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
August 25, 2012 at 6:37 am
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.
August 30, 2012 at 2:55 pm
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
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply