Script to Add User to all Databases

  • 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

  • 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

  • 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

    QUIGROUP- Need a Certified experienced DBA for a project or troubleshooting? Need help with programming, database recovery, performance tuning, ETL, SSRS or developing new databases? Contact us.. 1-786-273-9809

  • 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]'

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply