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

INSTEAD OF triggers on views

Last time I wrote about updateable views – a feature that is not considered by many database architects and developers when they implement data interface. The updateable views have some limitations what makes them useful in only specific situations. Two most important limitations in my opinion are that you cannot update columns from more than one table in single update statement and that any derived or calculated columns cannot be directly updated.
Luckily, there is a way to bypass these limitations by using INDSTEAD OF triggers on views. The triggers are fired as the name implies instead of DML statement, so you have to implement effective operation within the trigger. If the trigger is empty, no update, insert or delete will be made.
Let’s look at Person.vContact2 view again. The view returns columns ContactId, NameStyle, Full Title, FirstName, MiddleName, LastName and several others. The Full Title column is a column that returns custom information depending on the underlying Title column from the Person.Contact table. You cannot update the Full Title column directly, because it doesn’t exist in the Person.Contact. If you want to change the information returned by the view, you have to modify the Title column in the table. Of course, you can update the table directly or via stored procedure and in most cases this would be the preferred way, but in some cases updating the view has its benefits. You may also have a requirement that the tables must not be updateable directly and the views are the only interface you can use to modify the data.
So, to change the Full Title column in the view, you have to modify the Title value appropriately. As you remember, the definition of the view is as follows:

   1: ALTER view [Person].[vContact2]

   2: as

   3: select ContactID, NameStyle, 

   4: case when Title = 'Mr.' then 'Very long title 1'

   5:     when Title = 'Ms.' then 'Very long title 2'

   6:     else 'Other title' end [Full Title], 

   7: FirstName, MiddleName, LastName, Suffix, EmailAddress, EmailPromotion, Phone, PasswordHash, PasswordSalt, rowguid, ModifiedDate

   8: from Person.Contact

Lines 4-6 contain definition of the Full Title column. In my AdventureWorks database, ContactID has ‘Other title’ returned by the view. I want to change it to ‘Very long title 1’.

You can create triggers for INSERT, UPDATE and DELETE statements. One of the ways is to create one trigger for all three types of operation, the other is to create a separate trigger for each of types separately. The latter approach simplifies logic within the trigger a bit, on the expense of maintainability – you have three database objects to worry about instead of one.

This is sample trigger that is fired for every DML operation against the view:

   1: create trigger Person.trgVContact2 on Person.vContact2

   2: instead of insert, update, delete

   3: as

   4: begin

   5:     print 'Trigger called'

   6: end

Now let’s call an update on the view:

   1: select * from Person.vContact2 where ContactId = 4

   2: update Person.vContact2 set LastName = 'NoName' where ContactId = 4

   3: select * from Person.vContact2 where ContactId = 4

Nice. The trigger was fired as you can see in the output. But, if you look at the LastName column, it was not changed. This is because this is an instead of trigger and it replaces the original operation. To actually update the column,  you have to implement the trigger in more useful way:

   1: if exists (select 1 from sys.objects where object_id = object_id('Person.trgVContact2'))

   2: drop trigger Person.trgVContact2

   3: go

   4: create trigger Person.trgVContact2 on Person.vContact2

   5: instead of insert, update, delete

   6: as

   7: begin

   8:     if (@@rowcount = 0)

   9:     begin

  10:         print 'No rows matching criteria'

  11:         return

  12:     end

  13:     if exists(select 1 from inserted) and exists (select 1 from deleted) --update operationr

  14:     begin

  15:         update contacts set LastName = inserted.LastName

  16:         from Person.Contact contacts inner join inserted on contacts.ContactId = inserted.ContactId 

  17:     end

  18:     else if exists(select 1 from inserted) --insert operation

  19:     begin

  20:         return; --TODO: implement with useful logic

  21:     end

  22:     else --delete operation

  23:     begin

  24:         return; --TODO: implement with useful logic

  25:     end

  26: end

As you see, this trigger allows for updating only LastName column. This column this column is exposed by the view without any modifications. On a side, did you know that triggers are fired even when no rows are affected? This was a surprise for me a few months ago. You can prevent potentially costly code from running if you check if there is anything to process. Lines 8-12 show how it can be done.

Ok, what about Full Title and other columns? This is the modified code for update operation only:

   1: if exists(select 1 from inserted) and exists (select 1 from deleted) --update operationr

   2:     begin

   3:         if update(PasswordHash) or update(PasswordSalt)

   4:         begin

   5:             print 'Password data changes are not permitted'

   6:             return

   7:         end

   8:         update contacts 

   9:             set NameStyle = inserted.NameStyle,

  10:                 Title = case inserted.[Full Title]

  11:                             when 'Very long title 1' then 'Mr.'

  12:                             when 'Very long title 2' then 'Ms.'

  13:                             else contacts.Title --no change in this case

  14:                         end,

  15:                 FirstName = inserted.FirstName,

  16:                 MiddleName = inserted.MiddleName,

  17:                 LastName = inserted.LastName,

  18:                 Suffix = inserted.Suffix,

  19:                 EmailAddress = inserted.EmailAddress,

  20:                 EmailPromotion = inserted.EmailPromotion,

  21:                 Phone = inserted.Phone,

  22:                 ModifiedDate = current_timestamp

  23:         from Person.Contact contacts inner join inserted on contacts.ContactId = inserted.ContactId 

  24:     end

Triggers offer you opportunity to validate what columns are updated. In certain scenarios, you don’t want users to be able to modify sensitive data. This can be achieved using triggers for example. In lines 3-7 of the above script you can see code preventing update operation on PasswordSalt and PasswordHash operations.

Ok, so far, so good. Triggers on views are one of the methods of implementing logic required to update data. However usually it is better to do it using stored procedures. There are scenarios though when triggers on views give you quite interesting ways of implementing ETL. More about this in next post.

Technorati Tags: sql server,programming,trigger,DDL,DML,t-sql,code

Updateable views – how to use them

This post is first part of miniseries that discusses ways of updating data in views. In this post I will discuss updateable views, in the second part there will be short overview of INSTEAD OF triggers and interesting implications they offer.

Note: All examples in this post are made using AdventureWorks sample database, which you can download from CodePlex page.

With simple views selecting just from single table, with no derived columns the query engine knows how to translate insert or update statement on view to appropriate operation on underlying tables, and no trigger is required. The following script shows an example of such behaviour:

   1: select * from Person.vContact1 where ContactID = 1

   2: update Person.vContact1 set MiddleName = 'S.' where ContactID = 1

   3: select * from Person.vContact1 where ContactID = 1

The query engine translates update statement against the view to an update statement against the underlying table, and if you look at the execution plan of the update statement, you’ll see there is no difference to an update statement against the bare table:





If the view is more complex, the query engine doesn’t know how to execute the operation and the update fails. There may be several different error messages, depending on the type of the error. For example, if you have calculated column in your view, you cant update this column and obviously you can’t insert rows into the view. The following view returns more or less the same information as the Person.vContact1, but it replaces original value of MiddleName column with custom string:

   1: ALTER view [Person].[vContact2]

   2: as

   3: select ContactID, NameStyle, 

   4: case when Title = 'Mr.' then 'Very long title 1'

   5:     when Title = 'Ms.' then 'Very long title 2'

   6:     else 'Other title' end [Full Title], 

   7: FirstName, MiddleName, LastName, Suffix, EmailAddress, EmailPromotion, Phone, PasswordHash, PasswordSalt, rowguid, ModifiedDate

   8: from Person.Contact

If you try to update the Full Title column, the update will fail:

   1: select * from Person.vContact2 where ContactID = 1

   2: update Person.vContact1 set [Full Title] = 'Some other title' where ContactID = 1

   3: select * from Person.vContact2 where ContactID = 1

Msg 207, Level 16, State 1, Line 2
Invalid column name 'Full Title'.


This is obviously reasonable: Full Title column is calculated at runtime, when the view is executed and cannot be altered in any way because there is no storage related with this column – this column doesn’t exist in the table. You can however update other columns in this view, because they are bound directly to columns in the table.

   1: select * from Person.vContact2 where ContactID = 1

   2: update Person.vContact1 set MiddleName = 'D.' where ContactID = 1

   3: select * from Person.vContact2 where ContactID = 1

In some cases views return data from more than one table. Let’s examine such case. The following script updates City name in one of the tables that comprise the Purchasing.vVendor view:

   1: select * from Purchasing.vVendor where VendorId = 1

   2: select * from Person.Address where AddressLine1 = '683 Larch Ct.'

   3: update Purchasing.vVendor set City = 'Buenos Aires' where VendorId = 1

   4: select * from Person.Address where AddressLine1 = '683 Larch Ct.'

   5: select * from Purchasing.vVendor where VendorId = 1

When you run the above script you’ll notice that query engine was smart enough to update only appropriate row in the Person.Address table. Note also that the view,although joins eight tables, uses inner joins only and the query engine is able to determine exact range of rows to update in each participating table.

There are some other conditions that have to be met to make a view updateable, for example, when you run an update query, you have to modify only columns from one base table at a time. The following query fails:

   1: update Purchasing.vVendor 

   2:     set City = 'Buenos Aires',

   3:     MiddleName = 'M.' 

   4:     where VendorId = 1

Msg 4405, Level 16, State 1, Line 1
View or function 'Purchasing.vVendor' is not updatable because the modification affects multiple base tables.

Other conditions include (after MSDN):

• The columns being modified in the view must directly reference the underlying data in the table columns. The columns cannot be derived in any other way, such as through the following:
  
  
  • An aggregate function: AVG, COUNT, SUM, MIN, MAX, GROUPING, STDEV, STDEVP, VAR, and VARP.
  • A computation. The column cannot be computed from an expression that uses other columns. Columns that are formed by using the set operators UNION, UNION ALL, CROSSJOIN, EXCEPT, and INTERSECT amount to a computation and are also not updatable.
• The columns being modified are not affected by GROUP BY, HAVING, or DISTINCT clauses.
• TOP is not used anywhere in the select_statement of the view together with the WITH CHECK OPTION clause.

This will conclude first part of the series. In the next post I will write about triggers on views and their possible usage.

Technorati Tags: TSql,t-sql,SQL Server,CREATE VIEW,INSTEAD OF,AdventureWorks,Execution plan,code,programming,DDL

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

How to prepare sample data

Recently someone on one of SQL Server forums had a question how to prepare sample data so users can work on database restored from production. The requirement was that testers must not know the real data, names, addresses, emails and so on. On the other hand, users find very hard to work with totally random strings, like xwzr as first name, for example.
There are several tools on the market that help to prepare sample data. One of them is Visual Studio Database Edition, the other is SQL Data Generator from RedGate. I am sure there are many more.
The drawback of the above two excellent products is that they are not free. In some cases this is a major obstacle, because bosses are somehow not as likely to spend money for your tools (and toys) as you would like them to be.
So sometimes you have to implement sample data generator yourself. As it turns out, it is not too complicated (especially for simple cases).
Let’s assume that we want to replace all names in Person.Contact table in AdventureWorks database.
There are 19972 rows in this table in my database. I would like to replace all FirstName, MiddleName and LastName values with values that I prepare. Obviously, it is not so easy to come up with 20000 other names which don’t belong to Klingon language.
But if you think about this, 20000 is 50 x 40 x 10. If you have one fifty first names, forty last names and ten middle names, you can create 20000 unique combinations of these. You can easily find lists of names if you search, but for example this page contains all names that we might need to populate 20000 rows.
For this example, I created three tables, each for separate list of names:

   1: create table #RandomLastNames(RandomName nvarchar(50))

   2: create table #RandomFirstNames(RandomName nvarchar(50))

   3: create table #RandomMiddleNames(RandomName nvarchar(50))

I populated them using the names I found on the page I linked before. For middle names, I just typed in several initials and a NULL value, as NULLs are present in MiddleName column in Person Contact.

   1: insert #RandomMiddleNames (RandomName)

   2: select N'J.' union all

   3: select N'T.' union all

   4: select N'R.' union all

   5: select N'A.' union all

   6: select N'C.' union all

   7: select N'M.' union all

   8: select N'W.' union all

   9: select N'D.' union all

  10: select N'S.' union all

  11: select N'K.' union all

  12: select N'Z.' union all

  13: select NULL

To get number of combinations of the data you just select count from cartesian product of all three tables:

   1: select count(*) [No of combinations] from

   2: #RandomFirstNames cross join 

   3:     #RandomLastNames cross join 

   4:         #RandomMiddleNames

The rest is easy. To preview how data you have will be replaced you run this query:

   1: select cn.ContactID, cn.FirstName + N' ' + isnull(cn.MiddleName + N' ', N'') + cn.LastName OriginalData,

   2: (select top 1 RandomName from #RandomFirstNames where cn.ContactId = cn.ContactId order by newid()) FirstName, 

   3: (select top 1 RandomName from #RandomMiddleNames where cn.ContactId = cn.ContactId order by newid()) MiddleName, 

   4: (select top 1 RandomName from #RandomLastNames where cn.ContactId = cn.ContactId order by newid()) LastName

   5: into #DataMapping

   6: from Person.Contact cn

To actually update the data just run this update:

   1:  

   2: update Person.Contact set

   3: FirstName = b.FirstName,

   4: MiddleName = b.MiddleName,

   5: LastName = b.LastName

   6: from Person.Contact a inner join 

   7:         #DataMapping b on a.ContactId = b.ContactId

And that’s it! You can modify the script I attach to suit your needs, you can modify other data the same way, like emails, address lines etc.

Sample code download here.

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

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