Post reply

Add multiple users and give permissions to many databases

  • August 23, 2012 at 9:51 am

    #280656

    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

    #1529394

    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

  • August 23, 2012 at 12:01 pm

    #1529396

    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

    #1529399

    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

  • August 23, 2012 at 12:25 pm

    #1529402

    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

    #1529409

    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

  • August 23, 2012 at 12:54 pm

    #1529420

    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

    #1529434

    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

    #1530051

    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

    #1532138

    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