Piotr Rodak

Makesafe script - set up security for developers

I worked yesterday on a script that is supposed to run on DEV database after it has been restored from production. This script adjusts configuration data and security in the database so developers can work safely on production data without compromising security policies.

The original script has a long history, over 7 or 8 years. I think it's origins reach out as far as to SQL Server 7.0. Parts responsible for creating user accounts for developers were written with old, deprecated now syntax, like sp_grantdbaccess for example. I decided to modify this part to use CREATE USER and other, more contemporary language constructs.

I like to use queries that generate script for me. I wrote about such utility to generate create database snapshot script some time ago. Since the list of developers and potentially their role assignments in the make safe script may change over time, I thought that this will be pretty reasonable approach, the only difference be that instead of returning generated script to output window, saving it in a variable and later executing by sp_executesql.

The script has pretty simple structure. The first part of it contains declarations and initialization of structures that define logins, users and rights that have to be implemented. An example of the script is displayed below:

--This table contains list of logins to be mapped to databases.
    declare @DevLogins table
    (
      LoginName sysname, --name of login on the server 
      UserName sysname --name of user mapped to the login in databases
    )
    
    --PR: add or remove WINDOWS logins from here as aproppriate. Remember to modify @PermissionsToAdd table below.
	--LoginName is the domain name of the account, UserName is name of the user mapped to the login in all databases
    insert into @DevLogins(LoginName, UserName)
    select 'DOMAIN\Developer1', 'Developer1' union all --dev account
    select 'DOMAIN\Developer2', 'Developer2' union all --dev account
    select 'DOMAIN\s-Developer3', 's-Developer3' --application account

	--This table contains SQL logins and users used in databases
	declare @SQLLogins table
	(
		LoginName sysname,
		LoginPwd varchar(50),
		UserName sysname
	)

	insert into @SQLLogins(LoginName, LoginPwd, UserName)
	select 'devLogin1', 'pwd1', 'devUser1' union all --application account
	select 'devLogin2', 'pwd2', 'devUser2' --application account

I decided to split Windows logins and SQL Logins as they will usually have different rights assigned. SQL logins can be used for example by applications during development stage, for some bulk load operations and so on.

I also added a variable that will control if the generated script is executed or only printed:

 --used to decide if actions should be executed or only printed.
    declare @DEBUG char(1)
    set @DEBUG = 'N'

 You can see how it works in this piece of script for example:

    raiserror('---## Dropping DEV logins..', 10, 1 ) with nowait;
    select @RunSQL = convert(varchar(max), 
                      (select 'if exists(select 1 from sys.server_principals where name = '''+
                          LoginName+''')' + char(10) +
                          ' drop login ['+LoginName+'];' + 
							char(10) + char(10) 
                            from @DevLogins for xml path('')))
    
    
    raiserror(@RunSql, 10, 1) with nowait;
    
    if @DEBUG = 'N'
      exec sp_executesql @RunSQL
	raiserror('---## DEV logins dropped.', 10, 1 ) with nowait;

As you see, the above script generates drop login statements. It prints out the generated code and executes it only if the value of @DEBUG variable is set to 'N'. The script generates statements for the values defined earlier in the @DevLogins table.

 In my case, there are several databases that have to have security set up. Since the required rights are same in all databases (in development environment), I decided to put the database names into a variable as well:

--This table contains list of databases belonging to the system
    declare @APPDatabases table (DBName sysname)
    
    insert into @APPDatabases(DBName)
    select 'FEEDS' union all
    select 'REPORTING' union all 
    select 'PROCESSING' union all
    select 'ARCHIVE' 

 Require drole membership is covered by another table variable:

	-- Verify if all users defined in @DevLogins are refrenced in this table
	-- Add one row for each user-role pair. If user belongs to more than one role, two rows have to be added
	INSERT INTO @PermissionsToAdd(UserID, DBRole)
	select 'Developer1', 'DeveloperRole' union all
	select 'Developer2', 'DeveloperRole' union all
	select 's-Developer3', 'DeveloperRole'

 Note that if you want particular user to be a member of more than role, each assignment has to be added to the table.

Some developers should have to have special rights assigned, like ability to run profiler or investigate DMVs. These rights can be implemented in the following way:

	--this table contains names of custom grant statements required for all LOGINS.
	--DON'T add here execute rights to particular objects in databases
	declare @CustomRights table(GrantRight varchar(100))
	insert @CustomRights(GrantRight)
	select 'GRANT VIEW SERVER STATE ' union all --use dmvs
	select 'GRANT ALTER TRACE '  --run profiler

 The rights are assigned in the following script. Note there is also a way to skip certain logins from the assignment. You don't necessarily want to have application logins to have all rights required by developers.

    raiserror('---## Adjust DEV login rights..', 10, 1 ) with nowait;

    --exclude application account from custom rights
    declare @ExcludeAccounts table(LoginName sysname)
    insert @ExcludeAccounts(LoginName) values('DOMAIN\s-Developer3')

	

    select @RunSQL = convert(nvarchar(max), 'use master;'  + char(10) + char(10) +
 			convert(nvarchar(max), 
             (select GrantRight + ' TO ' + quotename(LoginName) +';' + char(10) from @DevLogins cross join @CustomRights
			where LoginName not in(select LoginName from @ExcludeAccounts) for xml path('')
			)
			) +  char(10))
  
    raiserror(@RunSql, 10, 1) with nowait;
    
    if @DEBUG = 'N'
      exec sp_executesql @RunSQL


    raiserror('---## DEV logins rights adjusted.', 10, 1 ) with nowait;

And that's about it. You can modify the script to fit your needs for various environments. Even if you have to put some work initially in creating the script and setting up proper security for users, later you will have definitely less problems during the configuration of the environments. In fact, this script can be used to configure QA and UAT users, not only developers. I attach the full script, if you find the idea useful, please download it and adjust it to your needs.

DISABLE TRIGGER not captured, SSIS

This post was to be about behavior of SSIS when they insert rows to tables with triggers on them. We had an issue with one of our packs that began to fail 'all of a sudden'. But when I began writing this post and building test SSIS project and database objects, I came across something even more interesting.

Here we go:

The pack was working properly for several months and all of a sudden one of the execution paths began to fail. I related this failure to change that I had made a few days before. I added trigger to one of the tables that was populated by the pack. The pack uuses bulk method to insert rows to the table. There are numerous resources about the issue: by default triggers are disabled during BULK INSERT. MSDN has a document about different bulk operation techniques and behavior of triggers. I found it interesting by the way, that OPENROWSET method treats triggers in opposite way to BULK INSERT and bcp. SSIS behaves like bcp when it comes to bulk operations. If you want to use triggers during bulk insert, you must add FIRE_TRIGGERS hint in the Advanced Editor of OLEDB destination.

Usually (when FIRE_TRIGGERS is not supplied), SSIS will disable triggers while inserting rows to a table using fast load option. To find out what is happening, I set up DDL trigger that records all DDL operation on test database. The code to create audit table and the trigger is shown below:

--–this table will store some information about user activity
CREATE TABLE DDLAudit
(
	dttime datetime,
	strservername VARCHAR(25),
	strusername VARCHAR(15),
	strchange VARCHAR(max)
)

GO

CREATE TRIGGER [trg_DDLAudit]
ON DATABASE 
FOR DDL_DATABASE_LEVEL_EVENTS 
AS
      SET NOCOUNT ON
      DECLARE @xEvent XML
      SET @xEvent = eventdata() 
      INSERT INTO DDLAudit VALUES(
            convert(datetime, convert(varchar(30), @xEvent.query('data(/EVENT_INSTANCE/PostTime)'))),
            CONVERT(VARCHAR(25), @xEvent.query('data(/EVENT_INSTANCE/ServerName)')),
            CONVERT(VARCHAR(15), @xEvent.query('data(/EVENT_INSTANCE/UserName)')),
            CONVERT(VARCHAR(MAX), @xEvent.query('data(/EVENT_INSTANCE/TSQLCommand/CommandText)'))
     )

GO

Since I have a CSV file with output of sys.dm_db_physical_index_stats DMV, I decided to create test table using code as follows:

select * into testbulk from sys.dm_db_index_physical_stats(db_id(), null, null, default, default)
where 1=0

GO

Then I created the history table and a (very simple) trigger for insert that copies the inserted rows from the work table testbulk to the history table.

select * into testbulkhistory from testbulk where 1=0

go

create trigger trg_audit1 on testbulk
for insert
as
	insert testbulkhistory
	select * from inserted

To test that the trigger works, I ran the following queries. It works! :)

insert testbulk
select top 1 * from testbulk

select * from testbulkhistory

Ok, now it's time for the SSIS package. I created pretty simple pack that pulls data from CSV file and stores them in the testdb.dbo.testbulk table.

I added Data Conversion and Derived Column components to adjust the data type, otherwise SSIS was complaining about conversion errors.

I set the data access mode to "Table or view - fast load".

Ok, I cleaned data in both tables and ran the pack.

delete testbulk
delete testbulkhistory

As expected, the DML trigger was disabled by SSIS. There were no data in testbulkhistory table. But I was surprised to see, that DDL trigger was not fired.

I launched Profiler to see what happens, but I didn't find any information. I added following events to the trace to see if I can catch the statement disabling DDL trigger:

I manually ran DROP TRIGGER and CREATE TRIGGER for the DDL trigger to see if I can catch the events.

As you see, the events were captured correctly. I tried to capture also statements DISABLE TRIGGER and ENABLE TRIGGER, but these events are not covered by the Objects event category. There is a bug reported regarding DISABLE TRIGGER not being an event. I think it is not correct that there are two ways of disabling a DML trigger and only one of them is indirectly logged in ALTER TABLE statement. In case of DDL triggers, you don't have even the option to call 'logged' syntax, DISABLE and ENABLE TRIGGER events are just invisible to profiler.

If you think this is a problem, vote on this bug.

I wonder if there is a way to monitor what happens with the triggers during BCP/SSIS loads.

I attach test SSIS project and sample data, so you can test this yourself.