Script to Add User to all Databases

• AndrewSQLDBA

  SSC-Insane

  Points: 21829

  More actions

  January 19, 2011 at 11:29 am

  #245563

  Hello Everyone

  I have lost my script that I use to add a user to all databases.

  Does anyone have one handy that you could share with me?

  Thanks

  Andrew SQLDBA

• Koen Verbeeck

  SSC Guru

  Points: 259215

  More actions

  January 20, 2011 at 5:19 am

  #1275801

  Is this what you're looking for?

  http://sqlserverplanet.com/security/add-users-to-database-script/

  (don't know if it works properly, I just did a quick google search)

  Need an answer? No, you need a question
  My blog at https://sqlkover.com.
  MCSE Business Intelligence - Microsoft Data Platform MVP

• Portes Ysaias

  SSCrazy

  Points: 2145

  More actions

  April 11, 2011 at 2:25 pm

  #1310101

  This script will add the user to each DB, but you must have the Login created first.

  It will add the user as a db_datareader role.

  sp_msforeachdb 'use [?]; CREATE USER [MyDBUser] FOR LOGIN [MyDBLogin];EXEC sp_addrolemember ''db_datareader'', ''MyDBUser'''

  Enjoy

  Ysaias Portes | Forward Thinkers Consulting
  sp_SQLFlightRecorder — open-source SQL Server diagnostic recorder | forwardthinkersconsulting.com

• alexandrialang

  Valued Member

  Points: 53

  More actions

  February 21, 2013 at 9:42 am

  #1590036

  That script doesn't actually work. But here is one that will:

  exec sp_msforeachdb 'use [?]; CREATE USER [UserName] FOR LOGIN [UserName];EXEC sp_addrolemember db_datareader, [UserName]'