Post reply

create role in all database

  • July 23, 2018 at 4:49 pm

    #372510

    Hello everyone
    I need to create a server sql server role in all database except the system base
    CREATE ROLE [R_SQL_dev]
    GO
    thank you for your help

  • July 24, 2018 at 11:03 am

    #1998845


    EXEC sys.sp_MSforeachdb
       @command1 = N'USE [?];IF ''?'' NOT IN(''msdb'', ''master'', ''tempdb'', ''model'') AND NOT EXISTS(SELECT principal_id
    FROM sys.database_principals dp
    JOIN sys.database_role_members drm ON dp.principal_id=drm.role_principal_id
    WHERE dp.name = ''Developer'') DROP ROLE IF EXISTS Developer;'
     , @replacechar = N'?'
     , @command2 =  N'USE [?];IF ''?'' NOT IN(''msdb'', ''master'', ''tempdb'', ''model'') AND NOT EXISTS(SELECT * FROM sys.database_principals WHERE name = ''Developer'') CREATE ROLE Developer;'
     , @command3 = N'USE [?];IF ''?'' NOT IN(''msdb'', ''master'', ''tempdb'', ''model'') GRANT SELECT, VIEW DEFINITION, EXEC TO Developer;

Viewing 2 posts - 1 through 1 (of 1 total)

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