SQL Jobs change job step proxy

The purpose of this script is to set a proxy on each job step to allow the owner of the job to not have access to the target database. This applies when a separate server is used for ETL and data storage. This also has a strong effect on the performance of the database server as significantly less memory has to be allocated to the Integration Services service.

This assume you can segregate the jobs by job category to make sure only the targeted step are changed. This could also be done easily by job owner.


USE MSDB
GO

SELECT * FROM sysproxies;
--Use this to restrict the jobs to only the categories you want to change
DECLARE @categoryMatch nvarchar(30) = 'test_%';
--Set the proxy value to the values desired in the above query
DECLARE @proxy_value nvarchar(10) = 2;
DECLARE @CurJobId nvarchar(50);
DECLARE @curStepId nvarchar(50);

SELECT sj.job_id, sjs.step_id, proxy_id
FROM sysjobs sj
INNER JOIN sysjobsteps sjs
ON sj.job_id = sjs.job_id
WHERE category_id IN ( select category_id FROM syscategories WHERE  name like @categoryMatch)
AND (sjs.proxy_id != @proxy_value OR proxy_id IS NULL);

DECLARE db_cursor CURSOR FOR
SELECT sj.job_id, sjs.step_id
FROM sysjobs sj
INNER JOIN  sysjobsteps sjs
ON sj.job_id = sjs.job_id
WHERE  category_id IN (SELECT category_id FROM syscategories WHERE  name like @categoryMatch)
AND (sjs.proxy_id != @proxy_value OR proxy_id IS NULL);


OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @CurJobId, @curStepId

WHILE @@FETCH_STATUS = 0
BEGIN
PRINT 'Job_ID:  ' + @curJobId + '     Job Step:  ' + @curStepId;
EXEC dbo.sp_update_jobstep
@job_id = @curJobId
,@step_id = @curStepId
,@proxy_id = @proxy_value;

    FETCH NEXT FROM db_cursor INTO @CurJobId, @curStepId
END

CLOSE db_cursor
DEALLOCATE db_cursor

Best update database owner tools

In my never ending quest to automate the setting of DB owners here is the best script yet. It assumes there is a user called [Domain User]/[Server name]Server running the server.

USE Master
GO

DECLARE @userName nvarchar(50);
DECLARE @userNamePostfix nvarchar(10) = 'Server'; --'Admin';

IF  CHARINDEX('\', @@SERVERNAME, 0) > 0
SET @userName = '[Domain User]\' +  SUBSTRING(@@SERVERNAME, 0, CHARINDEX('\', @@SERVERNAME, 0)) + @userNamePostfix ;
ELSE
SET @userName = '[Domain User]\' +  @@SERVERNAME + @userNamePostfix;

--DECLARE @userName nvarchar(50) = '[Domain User]\' +  SUBSTRING(@@SERVERNAME, 0, CHARINDEX('\', @@SERVERNAME, 0)) + 'Admin';
-- Use this section to override if the pattern does not work
--SET @userName = 'override User';

PRINT @userName;

--Require the user to exist and be a sysadmin
IF EXISTS(SELECT sid FROM sys.syslogins WHERE name = @userName AND sysAdmin = 1)
BEGIN

--Should be modified to ignore DBs which are not assigned to a defaulte value
DECLARE @prefix  nvarchar(200) = 'IF ''?'' NOT IN (SELECT name from sys.databases WHERE owner_sid = SUSER_ID(''' + @userName + ''') OR DB_NAME(database_id) IN (''master'', ''msdb'',''model'', ''tempdb'', ''distribution'')) BEGIN  ';
DECLARE @postfix  nvarchar(50) = ' END';
DECLARE @cmd  nvarchar(512);

SET @cmd =  @prefix + 'PRINT ''?''; USE ?  exec sp_changedbowner @loginame=''' + @userName + ''' ' + @postfix;
PRINT @cmd;
EXEC master.sys.sp_MSforeachdb  @command1 = @cmd;
PRINT 'Successfully set DB owner to ' + @userName;
END
ELSE
BEGIN
  PRINT @userName + ' NOT found';
END

Adding Bulk Rights SQL 2005

Was trying to do some quick administration and realized the SQL Server 2005 had a different method than 2012. Do not put any [] brackets in the user name. However, make sure you put brackets around the DB name especially with Sharepoint to handle the dashed in the DB name.


DECLARE @cmd nvarchar(255);

--first create user
SET @cmd = 'IF ''[?]'' NOT IN(''master'', ''model'', ''msdb'', ''tempdb'') 
BEGIN USE [?] CREATE USER [domain\user] FOR LOGIN [domain\user]''  END';

EXEC master.sys.sp_MSforeachdb  @command1 = @cmd

--second assign role
SET @cmd = 'IF ''[?]'' NOT IN(''master'', ''model'', ''msdb'', ''tempdb'') 
BEGIN USE [?] exec sp_addrolemember ''db_owner'', ''Domain\User''  END';

EXEC master.sys.sp_MSforeachdb  @command1 = @cmd

Adding bulk rights improved

Obviously the system databases normally do not have a demand for bulk users and should be excluded from these updates. This is the syntax. This script changes the db owner in all but the system to sa to make sure a user is not the owner.

DECLARE @cmd nvarchar(255);

SET @cmd = 'IF ''?'' NOT IN(''master'', ''model'', ''msdb'', ''tempdb'') 
BEGIN USE ? exec sp_changedbowner sa  END';

EXEC master.sys.sp_MSforeachdb  @command1 = @cmd

Add Database Encryption User

USE Master
GO
--Assign Password to the instance
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'password';

--Create the certificate for the database
CREATE CERTIFICATE tdeCert WITH SUBJECT = 'TDE_Certificate';

--Create a backup to allow the password to be recovered
BACKUP CERTIFICATE tdeCert TO FILE = 'E:\backups\20140221_tdeCert.crt'
WITH PRIVATE KEY (
FILE = 'tdeCert',
ENCRYPTION BY PASSWORD = 'Password123');

USE [target database]
GO

--Use the certificate produced to assign it to the database
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_256
ENCRYPTION BY SERVER CERTIFICATE tdeCert;

--Start the encryption
ALTER DATABASE [target database] SET ENCRYPTION ON;

Database security and role management

During the SQL Server 2012 class I was working to finally nail down options for managing security across the numerous web platforms. One this to note is there is no good reason why dbo cannot own these roles as they should be created for reuse. The exception would be the single use like settings where it may make more sense to isolate at a lower level. It would take a typical and look like this

The disadvantage of this method is unless you include db_datareader or db_datawriter every object must be added by hand. however it can be used for masking tables and source objects from users. To set below allow the SP to be shown and executed.

Final screen shot of the role allowing access but locking the user out of changes and the definition

 

The masking for select would be as below