Monday, June 5, 2017

SQL script add user to all content database

SET NOCOUNT ON;

DECLARE @user_name    SYSNAME
        , @login_name SYSNAME;

SELECT @user_name = ‘Domain\Account’,
       @login_name = ‘Domain\Account’

SELECT '
    USE ' + QUOTENAME(NAME) + '
CREATE USER ' + QUOTENAME(@user_name)
       + '
    ALTER ROLE [db_owner] ADD MEMBER [Domain\Account]
    GO
 
'
FROM   sys.databases
WHERE  database_id > 4

       AND state_desc = 'ONLINE'

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

  • execute the results to text then can copy and paste to run it 
  • excluded the system database