Script to add a server login to multiple databases

• johnnyrmtl

  Hall of Fame

  Points: 3505

  More actions

  February 9, 2015 at 9:40 am

  #294597

  Hello all,

  I have migrated over 700 databases to another server and now I have to add a specific user to all these databases and sync , would anyone have a script to add this user at once to all these databases^

  much appreciated

  JR

• Lowell

  SSC Guru

  Points: 323518

  More actions

  February 9, 2015 at 9:52 am

  #1776423

  the users themselves are contained in the databases themselves,

  if you are migrating, maybe you mean migrating logins to a new server via the ms proc sp_help_revlogins?

  for an existing instance, where i'm adding people to have access,

  here's exactly what i sue for adding users and roles, across all databases.

  i use a able for specific databases and a different table for my new users.

  in my case, i add a role to every database, and add the user, then add the user to that role.

  --#################################################################################################

  --Create Table holding new users that will receive global read only access across ALL databases on this server

  --#################################################################################################

  IF OBJECT_ID('tempdb.[dbo].[#DBs]') IS NOT NULL

  DROP TABLE [dbo].[#DBs]

  CREATE TABLE #DBs (DBName varchar(128))

  --all dbs?

  INSERT INTO #DBs(DBName)

  SELECT name from master.sys.databases WHERE database_id > 4 AND state_desc='ONLINE' AND source_database_id IS NULL

  --specific dbs:

  --INSERT INTO #DBs(DBName)

  --SELECT 'DWH_Alpha ' UNION ALL

  --SELECT'AccessAdmin' UNION ALL

  --SELECT'UserAnalysis' UNION ALL

  --SELECT'Zues'

  IF OBJECT_ID('tempdb.[dbo].[#Users]') IS NOT NULL

  DROP TABLE [dbo].[#Users]

  CREATE TABLE #Users (UserID varchar(128))

  INSERT INTO #Users(UserID)

  SELECT 'mydomain\user1' UNION ALL

  SELECT 'mydomain\user2' UNION ALL

  SELECT 'mydomain\user3'

  --#################################################################################################

  --Create Users if needed

  --#################################################################################################

  DECLARE @Rollback int = 0

  DECLARE @Commands VARCHAR(max)

  IF @Rollback = 0

  BEGIN

  SELECT @Commands = s.Colzs

  FROM(SELECT

  Colzs = STUFF((SELECT ' ' + 'IF NOT EXISTS(SELECT * FROM sys.server_principals where type_desc=''WINDOWS_LOGIN'' AND name = ''' + UserID + ''')

  CREATE LOGIN [' + UserID + '] FROM WINDOWS WITH DEFAULT_DATABASE=[master];

  '

  FROM [#Users]

  ORDER BY UserID

  FOR XML PATH('')

  ),1,1,'')

  ) s

  SELECT @Commands = REPLACE(@Commands,' ', CHAR(13));

  PRINT @Commands

  EXECUTE(@Commands)

  --#################################################################################################

  --GRANT VIEW SERVER STATE AND VIEW ANY DATABASE to these users

  --#################################################################################################

  SELECT @Commands = ' ' + s.Colzs

  FROM(SELECT

  Colzs = STUFF((SELECT ' GRANT VIEW SERVER STATE TO [' + UserID + '];

  GRANT VIEW ANY DATABASE TO [' + UserID + '];

  '

  FROM [#Users]

  ORDER BY UserID

  FOR XML PATH('')

  ),1,2,'')

  ) s

  SELECT @Commands = REPLACE(@Commands,' ', CHAR(13));

  PRINT @Commands

  EXECUTE(@Commands)

  --#################################################################################################

  --Create Role DevelopersReadAccess in all databases

  --#################################################################################################

  SELECT @Commands = s.Colzs

  FROM(SELECT

  Colzs = STUFF((SELECT ' ' + '

  USE [' + name + ']

  IF NOT EXISTS (select * from sys.database_principals where type_desc=''DATABASE_ROLE'' AND name = ''DevelopersReadAccess'')

  BEGIN

  CREATE ROLE DevelopersReadAccess

  EXEC sp_addrolemember ''db_datareader'',''DevelopersReadAccess'';

  END

  '

  FROM master.sys.databases

  WHERE database_id >4

  AND state_desc='ONLINE'

  AND source_database_id IS NULL

  AND name IN(SELECT DbName FROM #DBs)

  ORDER BY name

  FOR XML PATH('')

  ),1,1,'')

  ) s

  SELECT @Commands = REPLACE(@Commands,' ', CHAR(13));

  PRINT @Commands

  EXECUTE(@Commands)

  --#################################################################################################

  --Add these users to the database and also the role DevelopersReadAccess in all databases

  --#################################################################################################

  SELECT @Commands = s.Colzs

  FROM(SELECT

  Colzs = STUFF((SELECT ' ' + '

  USE [' + name + ']

  IF NOT EXISTS (select * from sys.database_principals where name = ''' + t.UserID + ''')

  BEGIN

  CREATE USER [' + t.UserID + '] FOR LOGIN [' + t.UserID + '];

  EXEC sp_addrolemember ''DevelopersReadAccess'',''' + t.UserID + ''';

  END

  '

  FROM master.sys.databases db

  CROSS JOIN #Users t

  WHERE db.database_id > 4

  AND db.state_desc='ONLINE'

  AND source_database_id IS NULL

  AND name IN(SELECT DbName FROM #DBs)

  ORDER BY db.name

  FOR XML PATH('')

  ),1,1,'')

  ) s

  SELECT @Commands = REPLACE(@Commands,' ', CHAR(13));

  PRINT @Commands

  EXECUTE(@Commands)

  END --IF

  ELSE

  BEGIN

  --#################################################################################################

  ----Remove Users for the same definitions above

  --#################################################################################################

  SELECT @Commands = s.Colzs

  FROM(SELECT

  Colzs = STUFF((SELECT ' ' + '

  USE [' + name + ']

  IF EXISTS (select * from sys.database_principals where name = ''' + t.UserID + ''')

  BEGIN

  DROP USER [' + t.UserID + '];

  END

  '

  FROM master.sys.databases db

  CROSS JOIN #Users t

  WHERE db.database_id > 4

  AND db.state_desc='ONLINE'

  AND source_database_id IS NULL

  AND name IN(SELECT DbName FROM #DBs)

  ORDER BY db.name

  FOR XML PATH('')

  ),1,1,'')

  ) s

  SELECT @Commands = REPLACE(@Commands,' ', CHAR(13));

  PRINT @Commands

  EXECUTE(@Commands)

  --#################################################################################################

  ----Remove Logins for the same definitions above

  --#################################################################################################

  SELECT @Commands = s.Colzs

  FROM(SELECT

  Colzs = STUFF((SELECT ' ' + 'IF EXISTS(SELECT * FROM sys.server_principals where type_desc=''WINDOWS_LOGIN'' AND name = ''' + UserID + ''')

  DROP LOGIN [' + UserID + '];

  '

  FROM [#Users]

  ORDER BY UserID

  FOR XML PATH('')

  ),1,1,'')

  ) s

  SELECT @Commands = REPLACE(@Commands,' ', CHAR(13));

  PRINT @Commands

  EXECUTE(@Commands)

  END

  --select *

  --FROM master.sys.databases

  -- WHERE database_id >4

  -- AND state_desc='ONLINE'

  edit: fixed syntax

  Lowell

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

• Perry Whittle

  SSC Guru

  Points: 234013

  More actions

  February 9, 2015 at 9:55 am

  #1776425

  johnnyrmtl (2/9/2015)

  ---

  Hello all,

  I have migrated over 700 databases to another server and now I have to add a specific user to all these databases and sync , would anyone have a script to add this user at once to all these databases^

  much appreciated

  JR

  windows login or sql login

  -----------------------------------------------------------------------------------------------------------

  "Ya can't make an omelette without breaking just a few eggs" 😉

• johnnyrmtl

  Hall of Fame

  Points: 3505

  More actions

  February 9, 2015 at 9:56 am

  #1776426

  Thanks for your response !

  Just to clarify... I added a new server login on a new instance and I would only need to add this login to over 700 databases with a specific naming convention . Can this script to this ?

• Perry Whittle

  SSC Guru

  Points: 234013

  More actions

  February 9, 2015 at 10:09 am

  #1776433

  johnnyrmtl (2/9/2015)

  ---

  Thanks for your response !

  Just to clarify... I added a new server login on a new instance and I would only need to add this login to over 700 databases with a specific naming convention . Can this script to this ?

  Windows or sql login???

  This may be useful

  exec sp_msforeachdb @command1 = 'use [?]

  IF (select name from sys.database_principals where name = ''a login'') IS NOT NULL

  begin

  alter user [a login] with login = [a login]

  print ''Login altered''

  end

  else

  begin

  create user [a login] from login [a login]

  exec sp_addrolemember ''db_datareader'', ''a login''

  print ''Login created''

  end'

  -----------------------------------------------------------------------------------------------------------

  "Ya can't make an omelette without breaking just a few eggs" 😉

• johnnyrmtl

  Hall of Fame

  Points: 3505

  More actions

  February 9, 2015 at 10:36 am

  #1776450

  SQL login 🙂

  So if database names are called *DBxx* and an existing user "ABC" would need to be a dbo of these.

• Perry Whittle

  SSC Guru

  Points: 234013

  More actions

  February 10, 2015 at 4:54 am

  #1776577

  johnnyrmtl (2/9/2015)

  ---

  SQL login 🙂

  My script above will work, don't forget to change the username and any roles you wish to grant

  -----------------------------------------------------------------------------------------------------------

  "Ya can't make an omelette without breaking just a few eggs" 😉

• johnnyrmtl

  Hall of Fame

  Points: 3505

  More actions

  February 10, 2015 at 6:50 am

  #1776612

  Perry Whittle (2/9/2015)

  ---

  johnnyrmtl (2/9/2015)

  ---

  Thanks for your response !

  Just to clarify... I added a new server login on a new instance and I would only need to add this login to over 700 databases with a specific naming convention . Can this script to this ?

  Windows or sql login???

  This may be useful

  exec sp_msforeachdb @command1 = 'use [?]

  IF (select name from sys.database_principals where name = ''a login'') IS NOT NULL

  begin

  alter user [a login] with login = [a login]

  print ''Login altered''

  end

  else

  begin

  create user [a login] from login [a login]

  exec sp_addrolemember ''db_datareader'', ''a login''

  print ''Login created''

  end'

  ok thanks ...

  The user already exists with a sysadmin role , so it would look like this ?

  exec sp_msforeachdb @command1 = 'use [?]

  IF (select name from sys.database_principals where name = ''ABC'') IS NOT NULL

  begin

  alter user [ABC] with login = [ABC]

  print ''Login altered''

  end

  --else

  --begin

  --create user [a login] from login [a login]

  --exec sp_addrolemember ''db_datareader'', ''a login''

  --print ''Login created''

• johnnyrmtl

  Hall of Fame

  Points: 3505

  More actions

  February 10, 2015 at 6:55 am

  #1776616

  Lowell (2/9/2015)

  ---

  the users themselves are contained in the databases themselves,

  if you are migrating, maybe you mean migrating logins to a new server via the ms proc sp_help_revlogins?

  for an existing instance, where i'm adding people to have access,

  here's exactly what i sue for adding users and roles, across all databases.

  i use a able for specific databases and a different table for my new users.

  in my case, i add a role to every database, and add the user, then add the user to that role.

  --#################################################################################################

  --Create Table holding new users that will receive global read only access across ALL databases on this server

  --#################################################################################################

  IF OBJECT_ID('tempdb.[dbo].[#DBs]') IS NOT NULL

  DROP TABLE [dbo].[#DBs]

  CREATE TABLE #DBs (DBName varchar(128))

  --all dbs?

  INSERT INTO #DBs(DBName)

  SELECT name from master.sys.databases WHERE database_id > 4 AND state_desc='ONLINE' AND source_database_id IS NULL

  --specific dbs:

  --INSERT INTO #DBs(DBName)

  --SELECT 'DWH_Alpha ' UNION ALL

  --SELECT'AccessAdmin' UNION ALL

  --SELECT'UserAnalysis' UNION ALL

  --SELECT'Zues'

  IF OBJECT_ID('tempdb.[dbo].[#Users]') IS NOT NULL

  DROP TABLE [dbo].[#Users]

  CREATE TABLE #Users (UserID varchar(128))

  INSERT INTO #Users(UserID)

  SELECT 'mydomain\user1' UNION ALL

  SELECT 'mydomain\user2' UNION ALL

  SELECT 'mydomain\user3'

  --#################################################################################################

  --Create Users if needed

  --#################################################################################################

  DECLARE @Rollback int = 0

  DECLARE @Commands VARCHAR(max)

  IF @Rollback = 0

  BEGIN

  SELECT @Commands = s.Colzs

  FROM(SELECT

  Colzs = STUFF((SELECT ' ' + 'IF NOT EXISTS(SELECT * FROM sys.server_principals where type_desc=''WINDOWS_LOGIN'' AND name = ''' + UserID + ''')

  CREATE LOGIN [' + UserID + '] FROM WINDOWS WITH DEFAULT_DATABASE=[master];

  '

  FROM [#Users]

  ORDER BY UserID

  FOR XML PATH('')

  ),1,1,'')

  ) s

  SELECT @Commands = REPLACE(@Commands,' ', CHAR(13));

  PRINT @Commands

  EXECUTE(@Commands)

  --#################################################################################################

  --GRANT VIEW SERVER STATE AND VIEW ANY DATABASE to these users

  --#################################################################################################

  SELECT @Commands = ' ' + s.Colzs

  FROM(SELECT

  Colzs = STUFF((SELECT ' GRANT VIEW SERVER STATE TO [' + UserID + '];

  GRANT VIEW ANY DATABASE TO [' + UserID + '];

  '

  FROM [#Users]

  ORDER BY UserID

  FOR XML PATH('')

  ),1,2,'')

  ) s

  SELECT @Commands = REPLACE(@Commands,' ', CHAR(13));

  PRINT @Commands

  EXECUTE(@Commands)

  --#################################################################################################

  --Create Role DevelopersReadAccess in all databases

  --#################################################################################################

  SELECT @Commands = s.Colzs

  FROM(SELECT

  Colzs = STUFF((SELECT ' ' + '

  USE [' + name + ']

  IF NOT EXISTS (select * from sys.database_principals where type_desc=''DATABASE_ROLE'' AND name = ''DevelopersReadAccess'')

  BEGIN

  CREATE ROLE DevelopersReadAccess

  EXEC sp_addrolemember ''db_datareader'',''DevelopersReadAccess'';

  END

  '

  FROM master.sys.databases

  WHERE database_id >4

  AND state_desc='ONLINE'

  AND source_database_id IS NULL

  AND name IN(SELECT DbName FROM #DBs)

  ORDER BY name

  FOR XML PATH('')

  ),1,1,'')

  ) s

  SELECT @Commands = REPLACE(@Commands,' ', CHAR(13));

  PRINT @Commands

  EXECUTE(@Commands)

  --#################################################################################################

  --Add these users to the database and also the role DevelopersReadAccess in all databases

  --#################################################################################################

  SELECT @Commands = s.Colzs

  FROM(SELECT

  Colzs = STUFF((SELECT ' ' + '

  USE [' + name + ']

  IF NOT EXISTS (select * from sys.database_principals where name = ''' + t.UserID + ''')

  BEGIN

  CREATE USER [' + t.UserID + '] FOR LOGIN [' + t.UserID + '];

  EXEC sp_addrolemember ''DevelopersReadAccess'',''' + t.UserID + ''';

  END

  '

  FROM master.sys.databases db

  CROSS JOIN #Users t

  WHERE db.database_id > 4

  AND db.state_desc='ONLINE'

  AND source_database_id IS NULL

  AND name IN(SELECT DbName FROM #DBs)

  ORDER BY db.name

  FOR XML PATH('')

  ),1,1,'')

  ) s

  SELECT @Commands = REPLACE(@Commands,' ', CHAR(13));

  PRINT @Commands

  EXECUTE(@Commands)

  END --IF

  ELSE

  BEGIN

  --#################################################################################################

  ----Remove Users for the same definitions above

  --#################################################################################################

  SELECT @Commands = s.Colzs

  FROM(SELECT

  Colzs = STUFF((SELECT ' ' + '

  USE [' + name + ']

  IF EXISTS (select * from sys.database_principals where name = ''' + t.UserID + ''')

  BEGIN

  DROP USER [' + t.UserID + '];

  END

  '

  FROM master.sys.databases db

  CROSS JOIN #Users t

  WHERE db.database_id > 4

  AND db.state_desc='ONLINE'

  AND source_database_id IS NULL

  AND name IN(SELECT DbName FROM #DBs)

  ORDER BY db.name

  FOR XML PATH('')

  ),1,1,'')

  ) s

  SELECT @Commands = REPLACE(@Commands,' ', CHAR(13));

  PRINT @Commands

  EXECUTE(@Commands)

  --#################################################################################################

  ----Remove Logins for the same definitions above

  --#################################################################################################

  SELECT @Commands = s.Colzs

  FROM(SELECT

  Colzs = STUFF((SELECT ' ' + 'IF EXISTS(SELECT * FROM sys.server_principals where type_desc=''WINDOWS_LOGIN'' AND name = ''' + UserID + ''')

  DROP LOGIN [' + UserID + '];

  '

  FROM [#Users]

  ORDER BY UserID

  FOR XML PATH('')

  ),1,1,'')

  ) s

  SELECT @Commands = REPLACE(@Commands,' ', CHAR(13));

  PRINT @Commands

  EXECUTE(@Commands)

  END

  --select *

  --FROM master.sys.databases

  -- WHERE database_id >4

  -- AND state_desc='ONLINE'

  edit: fixed syntax

  Sorry for the confusion ...

  I have a user with sysadmin rights on an instance and I need to add him as dbo to a large amount of databases that were just migrated over to this instance .

• Perry Whittle

  SSC Guru

  Points: 234013

  More actions

  February 11, 2015 at 4:05 am

  #1776872

  if the user exists as a member of the sysadmin role you do not need to map them into databases, they can do what the hell they like anyway!!

  -----------------------------------------------------------------------------------------------------------

  "Ya can't make an omelette without breaking just a few eggs" 😉

• johnnyrmtl

  Hall of Fame

  Points: 3505

  More actions

  February 11, 2015 at 7:22 am

  #1776928

  Perry Whittle (2/11/2015)

  ---

  if the user exists as a member of the sysadmin role you do not need to map them into databases, they can do what the hell they like anyway!!

  indeed 🙂 without having to add it to the db as a login.