Disabling audit triggers

This is a follow up to the posts about audit triggers. I wrote about them some time ago, on my previous blogging platform. The first post discussed COLUMS_UPDATED() function that is very useful in triggers if you want to find out which columns are affected by DML operation. Second post showed how you can create an audit framework that subsequently is used to log what happens to data when users modify it.

Now, there are situations, when you want to make changes to a table, but you don’t want to have these operations logged. And I am not talking about criminal activities – but for example, a table is populated daily by ETL process which updates or inserts thousands of rows. Sometimes it doesn’t make sense to log all these operations – after all triggers incur some performance degradation on the database.

There are several ways to disable a trigger. First, you can use disable trigger statement This approach has one drawback – when you disable trigger, it is disabled for every user who modifies the data. So you may loose audit entries while your ETL is running. In some scenarios it’s not an option.

Alternatively you can implement trigger to check for certain condition and decide whether DML operation should be audited or not. There are several ways of doing this. Two are described by Itzik Ben-Gan in his excellent Inside SQL Server 2005 - T-SQL Programming. Triggers can check if a table with specific name exists in the temp database or use session context. The disadvantage of these options is that you have to modify code external to triggers, for example your stored procedures or batches.

You can check if there is certain entry in a configuration table. For example you can have table Audit.tIgnoreUsers which will contain user names for which you don't want to audit. Then, there’s a simple query to decide if the trigger should proceed or not:

   1: if(exists (select 1 from Audit.tIgnoreUsers where UserName = suser_name()))

   2:     return;

Alternatively, you can check if particular user belongs to particular role. To do this, you have to create role first and add user that you want to exclude from auditing.

   1: if not exists (select 1 from sys.database_principals where name = 'ExcludeFromAudit' and type='R')

   2:         exec sp_executesql N'create role ExcludeFromAudit authorization dbo'

   3:  

   4: go

   5:  

   6: exec sp_addrolemember 'ExcludeFromAudit', 'ETLLogin'

   7:  

   8: go

   9:  

  10: raiserror('Logins skipped in auditing: ', 10, 1) with nowait;

  11:  

  12: exec sp_helprolemember 'ExcludeFromAudit'

Then, in the audit trigger you just add code somewhere on the top of the trigger:

   1: if(is_rolemember('ExcludeFromAudit') = 1)

   2: begin

   3:     --print 'Audit skipped for user ' + suser_name()

   4:     return

   5: end

And that’s it. I find this way easier to manage, as typically users do not have rights to modify roles. The good practice is to have separate login for ETL from other logins, so you can audit every action that for example front end users perform, but the ETL stays almost unaffected.

Please note that if you happen to be sysadmin and want to test the is_rolemember on a user defined role, it will always return 0. Quite confusing, I know. There is a Connect item for IS_MEMBER function which behaves in the similar way, by design.

Technorati Tags: SQL Server,tips and tricks,code,programming,T-SQL

Things I like – generate DROP .. CREATE scripts

I will be writing about things I like from time to time. That is SQL Server things - other than beer, good books and company.
For today, one small thing but how useful. In SQL Server Management Studio 2008 you can script objects as DROP and CREATE in one go:

This is very useful for us, as we store all database objects in our SVN as DROP..CREATE scripts. Using this feature saves quite a bit of time each time we want to update the source control.
I noticed an interesting behavior - if you script your table this way, all constraints defined in the table are scripted with drop statements before the actual DROP TABLE statement is included. This is quite handy as you can easily modify this script to change constraint names or definitions without having to script them separately.

Technorati Tags: SQL Server,tsql,t-sql,code,tips and tricks

Coding practices - Call your constraints

I wrote a few weeks ago about unit tests and good practice of naming your constraints. I think that most of the developers are used to call primary keys and unique constraints, sometimes foreign keys. But in most of the code I’ve seen the CHECK and DEFAULT constraints remain unnamed. You might wonder, why bother? The answer is that as long as you don’t have to drop the constraints, everything is OK – apart from cryptic error messages if the constraints are violated. The problem begins if for some reason you need to drop the constraint – for example to change range of the allowed values or default value of a column. The other scenario is also painful – if you want to compare two databases and you script out objects with constraint names. If you let SQL Server to name your constraints, each database you create from your scripts is different. And you have different error messages in production than in UAT for example. Not a very good idea for troubleshooting. Let’s suppose you create a table for storing order summaries.

   1: create table OrderSummary

   2: (

   3: OrderID int not null,

   4: OrderValue decimal(10,2) not null,

   5: OrderCurrency char(3) check (OrderCurrency in ('EUR', 'USD')),

   6: OrderDate datetime not null default getdate()

   7: )

Looks nice. But to drop the constraint on the OrderCurrency column, you have to use dynamic sql:

   1: select * from sys.check_constraints

   2: declare @sql nvarchar(200)

   3: select @sql = N'alter table OrderSummary drop constraint ' + 

   4:         quotename((select name from sys.check_constraints 

   5:             where parent_object_id = object_id('OrderSummary')))

   6:  

   7: --print @sql

   8: exec sp_executesql @sql

Please note that the above script works only if you have one unnamed check constraint. If you have more constraints, you have to write more complex code. Not a very nice option if you want to have some control on which constraints you want to drop and which are to stay. Now if you run the following script, you will see that each time you create the table, names of constraints are different:

   1: use master

   2: go 

   3: create database devDB

   4: go

   5: use devDB

   6: go

   7: --simulate various test objects creating and dropping in DEV database

   8: create table OrderSummaryTest

   9: (

  10: OrderID int not null,

  11: OrderCurrency char(3) check (OrderCurrency in ('EUR', 'USD')),

  12: OrderDate datetime not null default getdate()

  13: )

  14: drop table OrderSummaryTest

  15:  

  16: go

  17:  

  18: create table OrderSummary

  19: (

  20: OrderID int not null,

  21: OrderValue decimal(10,2) not null,

  22: OrderCurrency char(3) check (OrderCurrency in ('EUR', 'USD')),

  23: OrderDate datetime not null default getdate()

  24: )

  25: go

  26:  

  27: select convert(varchar(10), db_name()) [test], 

  28:     name from sys.check_constraints 

  29:     where parent_object_id = object_id('OrderSummary')

  30:  

  31: go

  32:  

  33: create database prodDB

  34:  

  35: go

  36:  

  37: use prodDB

  38:  

  39: go

  40:  

  41: create table OrderSummary

  42: (

  43: OrderID int not null,

  44: OrderValue decimal(10,2) not null,

  45: OrderCurrency char(3) check (OrderCurrency in ('EUR', 'USD')),

  46: OrderDate datetime not null default getdate()

  47: )

  48:  

  49: go

  50:  

  51: select convert(varchar(10), db_name()) [test], 

  52:     name from sys.check_constraints 

  53:     where parent_object_id = object_id('OrderSummary')

  54:  

  55: go

  56:  

  57: ----cleanup

  58: use master

  59: go

  60: drop database devDB

  61: drop database prodDB

  62: go

As you see, the constraint names are different and UGLY. Note that I simulated object creation and destruction in devDB, because if you comment lines 7 – 14, you may get the same object ids for the constraints. But this never happens in real life. If you want to avoid such problems and make your and your dba’s life easier, give your constraints names, like in the following script:

   1: create table OrderSummary

   2: (

   3: OrderID int not null,

   4: OrderValue decimal(10,2) not null,

   5: OrderCurrency char(3) constraint CHK_OrderCurrencyRange check (OrderCurrency in ('EUR', 'USD')),

   6: OrderDate datetime not null constraint DF_OrderDate default getdate()

   7: )

This way, to drop the default constraint you just have to call:

   1: alter table OrderSummary drop constraint [DF_OrderDate]

No dynamic SQL required, and code is 100% same in production and in DEV. To sum it up, if you get the habit of assigning names to ALL your constraints, your code will look better and others will be able to understand it faster, what is important when troubleshooting production issues.

Technorati Tags: tsql,SQL Server,t-sql,programming,script,tips and tricks

sp_addrolemember – implicit create user with problems

This post was to be about an interesting issue I encountered a few days ago. I will write about this issue in the future, but I tried to reproduce it on my home laptop and while doing so, I came across behaviour I wasn’t aware of.
As you may know, using application roles simplifies your life. To add an user to application role you use sp_addrolemember procedure. Working on the script to reproduce my permissions issue, I created user in database and assigned it to custom role. When you create user explicitly, the default schema of the user will be dbo.

   1: use master

   2: go

   3: create database TestSchemaDb

   4: go

   5: create login frank with password='StrongestPassword', check_policy=off, check_expiration=off

   6: go

   7: use TestSchemaDb

   8: go

   9: create role WrapRole

  10: exec sp_addrolemember 'db_ddladmin', 'WrapRole'

  11: exec sp_addrolemember 'db_datawriter', 'WrapRole'

  12: exec sp_addrolemember 'db_datareader', 'WrapRole'

  13: grant execute to WrapRole

  14:  

  15: go

  16: ---create user and add him to the role

  17: create user frank from login frank

  18: go

  19: exec sp_addrolemember 'WrapRole', 'frank'

  20: go

You can verify this with the following script:

   1: ---create schema, frank

   2: execute as user='frank'

   3: select user_name() as [I am]

   4: go

   5: ---create table, frank

   6: create table tTable (a int, b int)

   7: go

   8: insert tTable(a, b) values(1, 2)

   9: select * from tTable

  10: ---but, where is the table?

  11: select object_schema_name(object_id('tTable')) as [Table in this schema]

  12: go

  13: revert

  14: select user_name() as [I am]

  15: go

The select statement in line 11 will return dbo schema name.

I noticed that when you don’t create an user but instead call sp_addrolemember, the user will be created in database. I thought that this was always true until yesterday, when I found out that this is the case only if you have a Windows login and create an user with the same name as the login. So for user frank, if we drop him from the database and try to create him using sp_addrolemember, we’ll receive an error:

   1: ---now second scenario

   2: drop user frank

   3: drop table tTable

   4: go

   5: --nothing returned

   6: select * from sys.database_principals where name='frank'

   7: go

   8: --this fails, frank doesn't exist in the database

   9: exec sp_addrolemember 'WrapRole', 'frank'

Msg 15410, Level 11, State 1, Procedure sp_addrolemember, Line 75
User or role 'frank' does not exist in this database.

For user that we add from a Windows login, everything works, at a first glance:

   1: ---third scenario

   2: create login [Amilo\Ola] from windows

   3: go

   4: --don't create user in database

   5: go

   6: -- call sp_addrolemember

   7: exec sp_addrolemember 'WrapRole', 'Amilo\Ola'

   8: go

I tried to connect as this user:

   1: ---create schema

   2: execute as user='Amilo\Ola'

   3: select user_name() as [I am]

   4: go

And I got error message:
Msg 916, Level 14, State 1, Line 3
The server principal "Amilo\Ola" is not able to access the database "TestSchemaDb" under the current security context.

Ok, so I granted the connect right to the user:

   1: grant connect to [Amilo\Ola]

The MSDN Help says that “if the new member is a Windows-level principal without corresponding user, new user is created, but may not be fully mapped to the login”. I wish I knew what “not fully mapped” means, besides that the user is created, but it doesn’t have the CONNECT right.
Now I was able to run the following script:

   1: ---create schema

   2: execute as user='Amilo\Ola'

   3: select user_name() as [I am]

   4: go

   5: ---create table

   6: create table tTable (a int, b int)

   7: go

   8: insert tTable(a, b) values(1, 2)

   9: select * from tTable

  10: ---but, where is the table?

  11: select object_schema_name(object_id('tTable')) as [Table in this schema]

  12: go

  13: revert

  14: select user_name() as [I am]

  15: go

If you run it, you will notice that the result of the query in line 11 will return Amilo\Ola equivalent for login you use for testing. The table will be, if you don't explicitly specify schema, located in default schema of the user. So, if you happen to create user with sp_addrolemember, the objects the user creates may not be in expected place. This may lead to additional lookups performed by db engine for queries which don’t explicitly specify schemas and possibly to access exceptions if for example procedure created by one user accesses table created by another.
There is another caveat of this behavior: If you try to drop user who owns a schema, you will get an exception:

   1: drop user [Amilo\Ola]

   2: go

Msg 15138, Level 16, State 1, Line 2
The database principal owns a schema in the database, and cannot be dropped.

You have to drop the schema first, and to do this, you have to transfer all objects in the schema to other location:

   1: drop schema [Amilo\Ola]

Msg 3729, Level 16, State 1, Line 2
Cannot drop schema 'Amilo\Ola' because it is being referenced by object 'tTable'.

To transfer objects to different schema you use ALTER SCHEMA statement.

   1: go

   2: alter schema dbo transfer [Amilo\Ola].[tTable]

   3: go

   4: drop schema [Amilo\Ola]

   5: go

   6: drop user [Amilo\Ola]

   7: go

Ok, this would conclude this post. In summary, you should check your scripts and databases and verify that the users are created with CREATE USER and with proper default schemas – it doesn’t always have to be the same schema as the users’s name.
Avoid implicit creation of users with sp_setapprole – this works only for Windows logins and you have to explicitly grant CONNECT right. Don’t use sp_adduser either – it will be removed in future version of SQL Server.

Objects created without explicit schemas will be created in user’s default schemas – and they may have pretty ugly names [Domain\Username].

You will run into various security issues when using these objects and when you try to drop users from database.

For completeness of the scripts on this page, here is the cleanup snippet:

   1: ---cleanup

   2: use master

   3: go

   4: drop database TestSchemaDb

   5: drop login frank

   6: drop login [Amilo\Ola]

   7: go

Technorati Tags: tips and tricks,SQL Server,T-SQL,programming

Save the time – use roles!

Some time ago I wrote about coding practices. I would like to follow this topic with a quick post about making the life easier.
If you happen to be a developer who also is responsible for managing development databases and access of other team members to the environment, you might find this tip useful.

Create role for developers and assign rights to it.

Usually you would want developers to be able to run DDL commands and also execute procedures and write and read data. I’ve seen many times that developers were given the db_owner role. This is in most cases too much. If you have specific custom role, you can easily adjust the security settings without having to browse through all users.

   1: use TestDB

   2: go

   3: create role [DBDeveloper] authorization dbo

   4: go

   5: grant execute to DBDeveloper

   6: go

   7: exec sp_addrolemember 'db_ddladmin', 'DBDeveloper'

   8: exec sp_addrolemember 'db_datawriter', 'DBDeveloper'

   9: exec sp_addrolemember 'db_datareader', 'DBDeveloper'

  10: go

As you see above, I create role DBDeveloper and grant several rights to the role. Next, I just need to add users to the role to give them what they need.

   1: use master

   2: go

   3: create login [frank] from windows with default_language=us_english 

   4: go

   5: use TestDB

   6: go 

   7: 

   8: create user [frank] from login [frank] with default_schema=dbo

   9: go

  10: exec sp_addrolemember 'DBDeveloper', 'frank'

  11: go

This little trick saves a lot of time and hassle, and if you save these scripts carefully, you can easily restore security on a database refreshed from production for example.

Technorati Tags: SQL Server,T-SQL,tips and tricks,programming