Post reply

Script to Add User to all Databases

  • 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

  • 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

  • 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

    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

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

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

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