SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Add multiple users and give permissions to many databases


Add multiple users and give permissions to many databases

Author
Message
PHXHoward
PHXHoward
SSCommitted
SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)

Group: General Forum Members
Points: 1867 Visits: 1264
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
Luis Cazares
Luis Cazares
SSC-Forever
SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)

Group: General Forum Members
Points: 42966 Visits: 19850
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.
General Disclaimer:
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?


How to post data/code on a forum to get the best help: Option 1 / Option 2
PHXHoward
PHXHoward
SSCommitted
SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)

Group: General Forum Members
Points: 1867 Visits: 1264
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?
Luis Cazares
Luis Cazares
SSC-Forever
SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)

Group: General Forum Members
Points: 42966 Visits: 19850
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.
General Disclaimer:
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?


How to post data/code on a forum to get the best help: Option 1 / Option 2
PHXHoward
PHXHoward
SSCommitted
SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)

Group: General Forum Members
Points: 1867 Visits: 1264
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.
Luis Cazares
Luis Cazares
SSC-Forever
SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)

Group: General Forum Members
Points: 42966 Visits: 19850
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.
General Disclaimer:
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?


How to post data/code on a forum to get the best help: Option 1 / Option 2
PHXHoward
PHXHoward
SSCommitted
SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)

Group: General Forum Members
Points: 1867 Visits: 1264
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.
RBarryYoung
RBarryYoung
SSC-Dedicated
SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)

Group: General Forum Members
Points: 35960 Visits: 9518
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."
belvdr
belvdr
Valued Member
Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)

Group: General Forum Members
Points: 51 Visits: 80
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.
Orlando Colamatteo
Orlando Colamatteo
SSC-Forever
SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)

Group: General Forum Members
Points: 41062 Visits: 14413
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search