Declare @username nvarchar(500), @cmd nvarchar(MAX), @password nvarchar(500)
SET @username = 'CeliveoDB2' --Change username
SET @password = 'UseStrongPAsswordhere' --change password

USE [master]

SET @cmd = 'CREATE LOGIN [' + @username + '] WITH PASSWORD = ''' + @password + ''''
--print @cmd
EXEC (@cmd)

SET @cmd = 'GRANT VIEW SERVER STATE to ' + @username + ''
--print @cmd
EXEC (@cmd)

USE [CeliveoDB]

SET @cmd ='IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE name = ''' + @username +''')
BEGIN
	CREATE USER [' + @username + '] FOR LOGIN [' + @username + '] WITH DEFAULT_SCHEMA=[dbo];
END'
--print @cmd
EXEC (@cmd)

EXEC sp_addrolemember @rolename = N'db_datareader', @membername = @username;

EXEC sp_addrolemember @rolename = N'db_datawriter', @membername = @username;

EXEC sp_addrolemember @rolename = N'db_ddladmin', @membername = @username;

-- Grant permission to EXECUTE stored procedures for CeliveoDB
SET @cmd = 'GRANT EXECUTE ON DATABASE::CeliveoDB TO [' + @username + ']'
--print @cmd
EXEC (@cmd)

USE [SJPS]

SET @cmd ='IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE name = ''' + @username +''')
BEGIN
	CREATE USER [' + @username + '] FOR LOGIN [' + @username + '] WITH DEFAULT_SCHEMA=[dbo];
END'
print @cmd
EXEC (@cmd)

EXEC sp_addrolemember @rolename = N'db_datareader', @membername = @username;

EXEC sp_addrolemember @rolename = N'db_datawriter', @membername = @username;

EXEC sp_addrolemember @rolename = N'db_ddladmin', @membername = @username;

-- Grant permission to EXECUTE stored procedures for CeliveoDB
SET @cmd = 'GRANT EXECUTE ON DATABASE::SJPS TO [' + @username + ']'
--print @cmd
EXEC (@cmd)
