grant access to all databases

  • 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

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

    Pradeep Adiga
    Blog: sqldbadiaries.com
    Twitter: @pradeepadiga

  • 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

  • Thank you, it was helpfull.

    Nira

  • Edit posted

  • 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/

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

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

  • 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