Code for multiple databases

Modified on Tue, 21 Apr at 2:50 PM

DECLARE @Domain nVARCHAR(MAX) = 'domain'
DECLARE @User nVARCHAR(MAX) = 'Synergetic Upgrades'

-- Create the login if it doesn't exist
IF NOT EXISTS (SELECT 1 FROM sys.server_principals WHERE name = @Domain + '\' + @User)
BEGIN
    EXEC ('USE [master]; CREATE LOGIN ['+@Domain+'\'+@User+'] FROM WINDOWS WITH DEFAULT_DATABASE=[master]')
END
ELSE
BEGIN
    PRINT 'Login ' + @Domain + '\' + @User + ' already exists.';
END

-- Grant sysadmin server role if the login doesn't already have it
IF NOT EXISTS (SELECT 1 FROM sys.server_role_members AS SRM JOIN sys.server_principals AS SP ON SRM.member_principal_id = SP.principal_id JOIN sys.server_principals AS SR ON SRM.role_principal_id = SR.principal_id WHERE SP.name = @Domain + '\' + @User AND SR.name = 'sysadmin')
BEGIN
    EXEC ('USE [master]; ALTER SERVER ROLE [sysadmin] ADD MEMBER ['+@Domain+'\'+@User+']')
END
ELSE
BEGIN
    PRINT 'Login ' + @Domain + '\' + @User + ' is already a sysadmin.';
END

-- Check if the user already exists in the MASTER database
IF NOT EXISTS (SELECT 1 FROM master.sys.database_principals WHERE name = @User AND type_desc = 'SQL_USER' AND authentication_type_desc = 'INSTANCE')
BEGIN
    -- Create the user in the MASTER database and grant db_owner role
    BEGIN TRY
        EXEC ('USE [master]; CREATE USER ['+@Domain+'\'+@User+'] FOR LOGIN ['+@Domain+'\'+@User+']')
        EXEC ('USE [master]; ALTER ROLE [db_owner] ADD MEMBER ['+@Domain+'\'+@User+']')
    END TRY
    BEGIN CATCH
        -- Handle any errors, e.g., print an error message
        PRINT 'Error creating user in database MASTER: ' + ERROR_MESSAGE();
    END CATCH
END
ELSE
BEGIN
    PRINT 'User ' + @User + ' already exists in database MASTER';
END

-- Get a list of all databases
DECLARE @Databases TABLE (DatabaseName nVARCHAR(MAX));
INSERT INTO @Databases (DatabaseName)
SELECT name
FROM sys.databases
WHERE name LIKE 'synergetic%';
-- Loop through each database
DECLARE @Database nVARCHAR(MAX);
WHILE EXISTS (SELECT 1 FROM @Databases)
BEGIN
    SELECT TOP 1 @Database = DatabaseName FROM @Databases;

    -- Check if the user already exists in the current database
    IF NOT EXISTS (SELECT 1 FROM sys.database_principals WHERE name = @User AND type_desc = 'SQL_USER' AND authentication_type_desc = 'INSTANCE')
    BEGIN
        -- Create the user in the current database and grant db_owner role
        BEGIN TRY
            EXEC ('USE [' + @Database + ']; CREATE USER ['+@Domain+'\'+@User+'] FOR LOGIN ['+@Domain+'\'+@User+']')
            EXEC ('USE [' + @Database + ']; ALTER ROLE [db_owner] ADD MEMBER ['+@Domain+'\'+@User+']')
        END TRY
        BEGIN CATCH
            -- Handle any errors, e.g., print an error message
            PRINT 'Error creating user in database ' + @Database + ': ' + 
ERROR_MESSAGE();
        END CATCH
    END
    ELSE
    BEGIN
        PRINT 'User ' + @User + ' already exists in database ' + @Database;
    END

    -- Remove the processed database from the list
    DELETE TOP (1) FROM @Databases;
END

Code for ICON, Need to DELETE Later.

DECLARE @Domain NVARCHAR(MAX) = 'icon'
DECLARE @User NVARCHAR(MAX) = 'synsdtnonprod'

-- Create the login if it doesn't exist
IF NOT EXISTS (SELECT 1 FROM sys.server_principals WHERE name = @Domain + '\' + @User)
BEGIN
    EXEC ('USE [master]; CREATE LOGIN ['+@Domain+'\'+@User+'] FROM WINDOWS WITH DEFAULT_DATABASE=[master]')
END
ELSE
BEGIN
    PRINT 'Login ' + @Domain + '\' + @User + ' already exists.';
END

-- Grant sysadmin server role if the login doesn't already have it
IF NOT EXISTS (SELECT 1 FROM sys.server_role_members AS SRM JOIN sys.server_principals AS SP ON SRM.member_principal_id = SP.principal_id JOIN sys.server_principals AS SR ON SRM.role_principal_id = SR.principal_id WHERE SP.name = @Domain + '\' + @User AND SR.name = 'sysadmin')
BEGIN
    EXEC ('USE [master]; ALTER SERVER ROLE [sysadmin] ADD MEMBER ['+@Domain+'\'+@User+']')
END
ELSE
BEGIN
    PRINT 'Login ' + @Domain + '\' + @User + ' is already a sysadmin.';
END

-- Check if the user already exists in the MASTER database
IF NOT EXISTS (SELECT 1 FROM master.sys.database_principals WHERE name = @User AND type_desc = 'SQL_USER' AND authentication_type_desc = 'INSTANCE')
BEGIN
    -- Create the user in the MASTER database and grant db_owner role
    BEGIN TRY
        EXEC ('USE [master]; CREATE USER ['+@Domain+'\'+@User+'] FOR LOGIN ['+@Domain+'\'+@User+']')
        EXEC ('USE [master]; ALTER ROLE [db_owner] ADD MEMBER ['+@Domain+'\'+@User+']')
    END TRY
    BEGIN CATCH
        -- Handle any errors, e.g., print an error message
        PRINT 'Error creating user in database MASTER: ' + ERROR_MESSAGE();
    END CATCH
END
ELSE
BEGIN
    PRINT 'User ' + @User + ' already exists in database MASTER';
END

-- Get a list of all databases
DECLARE @Databases TABLE (DatabaseName nVARCHAR(MAX));
INSERT INTO @Databases (DatabaseName)
SELECT name
FROM sys.databases
WHERE name LIKE 'synergetic%';

-- Loop through each database
DECLARE @Database nVARCHAR(MAX);
WHILE EXISTS (SELECT 1 FROM @Databases)
BEGIN
    SELECT TOP 1 @Database = DatabaseName FROM @Databases;

    -- Check if the user already exists in the current database
    IF NOT EXISTS (SELECT 1 FROM sys.database_principals WHERE name = @User AND type_desc = 'SQL_USER' AND authentication_type_desc = 'INSTANCE')
    BEGIN
        -- Create the user in the current database and grant db_owner role
        BEGIN TRY
            EXEC ('USE [' + @Database + ']; CREATE USER ['+@Domain+'\'+@User+'] FOR LOGIN ['+@Domain+'\'+@User+']')
            EXEC ('USE [' + @Database + ']; ALTER ROLE [db_owner] ADD MEMBER ['+@Domain+'\'+@User+']')
        END TRY
        BEGIN CATCH
            -- Handle any errors, e.g., print an error message
            PRINT 'Error creating user in database ' + @Database + ': ' + ERROR_MESSAGE();
        END CATCH
    END
    ELSE
    BEGIN
        PRINT 'User ' + @User + ' already exists in database ' + @Database;
    END

    -- Remove the processed database from the list
    DELETE TOP (1) FROM @Databases;
END