Want to Create a User and Assign Permissions in Multiple Databases at once

• lmarkum

  SSCertifiable

  Points: 7989

  More actions

  January 24, 2019 at 8:56 am

  #416652

  I have about 60 SQL Servers and I have an AD group that has Developers in it.  I'm trying to build a script that will loop through the user databases and create a user and assign permissions for that user so that I can quickly add a group to control Developer perms.  I'm first checking if the Login at the Server level exists and making it if it doesn't.

  
IF SUSER_ID('[Domain\IT Developers]') IS NULL
CREATE LOGIN [Domain\IT Developers] FROM WINDOWS WITH DEFAULT_DATABASE=[master]

  DECLARE @Command VARCHAR(8000)

  SELECT @Command = 'IF ''?'' NOT IN (''master'', ''model'', ''msdb'', ''tempdb'',''SSISDB'') BEGIN USE ? IF (SELECT DATABASE_PRINCIPAL_ID(''Domain\IT Developers'')) IS NULL
        CREATE USER [Domain\IT Developers] FOR LOGIN [Domain\IT Developers];
    CREATE USER [Domain\IT Developers] FOR LOGIN [Domain\IT Developers]

  --add perms to the user in that DB context
    EXEC sp_addrolemember N''db_datareader'', N''Domain\IT Developers''
    EXEC sp_addrolemember N''db_datawriter'', N''Domain\IT Developers''
    GRANT VIEW DEFINITION TO [Domain\IT Developers] END '
PRINT @Command
EXEC sp_MSforeachdb @Command

  When I run this I get an error telling me that the user already exists, but it doesn't because I've looked in the SSMS UI on the instance I'm testing and I know it isn't there.  Any suggestions or corrections to what I'm doing?

• John Mitchell-245523

  SSC Guru

  Points: 148809

  More actions

  January 24, 2019 at 9:16 am

  #2019988

  Try removing the square brackets from the first line.  It's looking for that literal login name - square brackets and all.

  John

• lmarkum

  SSCertifiable

  Points: 7989

  More actions

  January 24, 2019 at 10:06 am

  #2020000

  John Mitchell-245523 - Thursday, January 24, 2019 9:16 AM

  Try removing the square brackets from the first line.  It's looking for that literal login name - square brackets and all.

  John

  The login creation part of this isn't where the issue is, but I removed the brackets from it anyway.  Now I'm getting strange behavior where the user doesn't exist but I get a message that it does already exist.  Yet, the users are added and tied to the login.  I'm really confused about what's happening.

• ScottPletcher

  SSC Guru

  Points: 101192

  More actions

  January 24, 2019 at 10:23 am

  #2020006

  You have CREATE USER in the code twice (once IF'd and once not).

  
IF SUSER_ID('[Domain\IT Developers]') IS NULL
  CREATE LOGIN [Domain\IT Developers] FROM WINDOWS WITH DEFAULT_DATABASE=[master]

  EXEC sp_MSforeachdb '
IF ''?'' NOT IN (''master'', ''model'', ''msdb'', ''SSISDB'', ''tempdb'')
BEGIN
    USE [?]
    PRINT ''?''
    IF DATABASE_PRINCIPAL_ID(''Domain\IT Developers'') IS NULL
    BEGIN
        PRINT '' Creating user.''
        CREATE USER [Domain\IT Developers] FROM LOGIN [Domain\IT Developers];
    END /*IF*/
    PRINT '' Adding all permissions for the user.''
    EXEC sp_addrolemember N''db_datareader'', N''Domain\IT Developers''
    EXEC sp_addrolemember N''db_datawriter'', N''Domain\IT Developers''
    GRANT VIEW DEFINITION TO [Domain\IT Developers]
END /*IF*/
'

  SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

• lmarkum

  SSCertifiable

  Points: 7989

  More actions

  January 24, 2019 at 11:25 am

  #2020014

  ScottPletcher - Thursday, January 24, 2019 10:23 AM

  You have CREATE USER in the code twice (once IF'd and once not).

  
IF SUSER_ID('[Domain\IT Developers]') IS NULL
  CREATE LOGIN [Domain\IT Developers] FROM WINDOWS WITH DEFAULT_DATABASE=[master]

  EXEC sp_MSforeachdb '
IF ''?'' NOT IN (''master'', ''model'', ''msdb'', ''SSISDB'', ''tempdb'')
BEGIN
    USE [?]
    PRINT ''?''
    IF DATABASE_PRINCIPAL_ID(''Domain\IT Developers'') IS NULL
    BEGIN
        PRINT '' Creating user.''
        CREATE USER [Domain\IT Developers] FROM LOGIN [Domain\IT Developers];
    END /*IF*/
    PRINT '' Adding all permissions for the user.''
    EXEC sp_addrolemember N''db_datareader'', N''Domain\IT Developers''
    EXEC sp_addrolemember N''db_datawriter'', N''Domain\IT Developers''
    GRANT VIEW DEFINITION TO [Domain\IT Developers]
END /*IF*/
'

  Thanks Scott.  I apparently need better proofreading skills.