Very useful SQL Scripts related queries, topics and discussions
User avatar
By LaszloCseri
#379 Situation: Backup / Restore an iScala Company database from your Productive System into Test or Development system and always losing an associated SQL user against that/those databases? Creating pain, because other teams complaining (like 3rd Party Applications want to connect to your iScala DBs with this SQL user name). Not anymore.

The following MS SQL Maintenance Job will take care of this. Please feel free to use and modify for your needs.

Just change the following in the script:

    YOUR_SQL_USERNAME to your specific SQL user name you want to assign for your iScala Database(s) as DBO.
    iScala% to your exact iScala Database Name or if you want this for all your iScala DBs, then according to your DB naming conventions you can use it like prefix. (i.e. iScala% is valid for DBs like: iScalaDB, iScalaSweden, iScalaNorway, etc.).

Code: Select allUSE [msdb]
GO

/****** Object:  Job [iScala YOUR_SQL_USERNAME User AutoAssignment Job]    Script Date: 8/12/2015 12:22:29 PM ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object:  JobCategory [[Uncategorized (Local)]]]    Script Date: 8/12/2015 12:22:29 PM ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)
EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'iScala YOUR_SQL_USERNAME User AutoAssignment Job',
      @enabled=1,
      @notify_level_eventlog=0,
      @notify_level_email=0,
      @notify_level_netsend=0,
      @notify_level_page=0,
      @delete_level=0,
      @description=N'This SQL Job is responsible to assign YOUR_SQL_USERNAME SQL User to all iScala databases on Staging System and grant this user as db_owner.',
      @category_name=N'[Uncategorized (Local)]',
      @owner_login_name=N'sa', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object:  Step [YOUR_SQL_USERNAME AutoAssign]    Script Date: 8/12/2015 12:22:29 PM ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'YOUR_SQL_USERNAME AutoAssign',
      @step_id=1,
      @cmdexec_success_code=0,
      @on_success_action=1,
      @on_success_step_id=0,
      @on_fail_action=2,
      @on_fail_step_id=0,
      @retry_attempts=0,
      @retry_interval=0,
      @os_run_priority=0, @subsystem=N'TSQL',
      @command=N'DECLARE @DB_Name varchar(100)
DECLARE @Command nvarchar(MAX)
DECLARE database_cursor CURSOR FOR
SELECT name
FROM MASTER.sys.sysdatabases where name like ''iScala%''

OPEN database_cursor

FETCH NEXT FROM database_cursor INTO @DB_Name

WHILE @@FETCH_STATUS = 0
BEGIN
     SELECT @Command = ''use '' + @DB_Name +   
    '' IF NOT EXISTS (SELECT * FROM master.DBO.SYSUSERS WHERE NAME = N''''YOUR_SQL_USERNAME'''' ) '' +
     ''    BEGIN '' +
     ''     PRINT ''''Granting access to '' + @DB_Name + '' database to login YOUR_SQL_USERNAME'''''' +
     ''     CREATE USER YOUR_SQL_USERNAME FOR LOGIN YOUR_SQL_USERNAME '' +
     ''     EXEC sp_addrolemember N''''db_owner'''', N''''YOUR_SQL_USERNAME'''' '' +
     ''    END ELSE BEGIN   '' +
     ''     PRINT ''''Login YOUR_SQL_USERNAME already granted access '' + @DB_Name + '' database.''''   '' +
    ''     IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE name = N''''YOUR_SQL_USERNAME'''')'' +
     ''     BEGIN CREATE USER YOUR_SQL_USERNAME FOR LOGIN YOUR_SQL_USERNAME END '' +
     ''     EXEC sp_addrolemember N''''db_owner'''', N''''YOUR_SQL_USERNAME'''' '' +
     ''    END ''
      

     EXEC sp_executesql @Command
   

     FETCH NEXT FROM database_cursor INTO @DB_Name
END

CLOSE database_cursor
DEALLOCATE database_cursor


',
      @database_name=N'master',
      @flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'iScala_AUTOASSIGN_SCHED',
      @enabled=1,
      @freq_type=4,
      @freq_interval=1,
      @freq_subday_type=4,
      @freq_subday_interval=5,
      @freq_relative_interval=0,
      @freq_recurrence_factor=0,
      @active_start_date=20150213,
      @active_end_date=99991231,
      @active_start_time=0,
      @active_end_time=235959,
      @schedule_uid=N'46910480-4e4c-475d-9d9b-f9d31c378838'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:

GO


Best Regards,
Laszlo