Post reply

grant access to all databases

  • November 18, 2010 at 2:04 am

    #241403

    hello experts,

    i have around 600 databases in my server, a user need select access of all the databases. will i have to go one by one in all the dbs and create tht user and give datareader role to him. or is thr any shorter way to do so????

    thanks in advance

  • November 18, 2010 at 2:19 am

    #1252334

    Cross post. All responses at http://www.sqlservercentral.com/Forums/Topic1022684-391-1.aspx

    Pradeep Adiga
    Blog: sqldbadiaries.com
    Twitter: @pradeepadiga

  • November 18, 2010 at 5:33 am

    #1252420

    Hi,

    You have to apply below mention script with your all database..

    Syntax

    USE [DatabaseName]

    GO

    CREATE USER [UserName] FOR LOGIN [LoginName] WITH DEFAULT_SCHEMA =schema_name

    GO

    EXEC sp_addrolemember 'db_datareader', 'UserName'

    Script For All Databases

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

    USE master

    GO

    DECLARE @LoginName varchar(256)

    SET @LoginName ='YourLoginName'

    SELECT 'USE [' + Name + ']'

    + ';'

    + 'CREATE USER [' + @LoginName + '] FOR LOGIN [' + @LoginName + '] WITH DEFAULT_SCHEMA =dbo'

    + ';'

    + 'EXEC sp_addrolemember ''db_datareader'', '''+ @LoginName + ''''

    AS ScriptToExecute

    FROM sys.databases

    WHERE name NOT IN ('Master','tempdb','model','msdb') -- Avoid System Databases

    AND (state_desc ='ONLINE') -- Avoid Offline Databases

    AND (source_database_id Is Null) -- Avoid Database Snapshot

    ORDER BY Name

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

    Copy output of this select query and open new query window and then execute..

    Ram
    MSSQL DBA

  • July 4, 2012 at 9:54 am

    #1508839

    Thank you, it was helpfull.

    Nira

  • July 23, 2013 at 4:50 pm

    #1635162

    Edit posted

  • July 23, 2013 at 9:46 pm

    #1635194

    You arw getting this error because you have specified db_datareader, db_datawriter' as a single role and due to this sql server not able to identify it as a valid role..... Specify both roles seperatly and it will work

    _______________________________________________________________
    To get quick answer follow this link:
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

  • July 24, 2013 at 9:21 am

    #1635410

    600 databases on one instance?? That maintenance schedule must be fun!

  • July 26, 2013 at 10:18 am

    #1636236

    Typo my bad, it is 60 Databases...but we do have 200+ on 4 node cluster environment

  • March 17, 2015 at 12:56 am

    #1784411

    I'm trying to use your post to grant a group-user read access to all databases, but I get the error message below.

    Please assist

    Msg 15259, Level 16, State 1, Line 1

    The DEFAULT_SCHEMA clause cannot be used with a Windows group or with principals mapped to certificates or asymmetric keys.

Viewing 9 posts - 1 through 8 (of 8 total)

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