Thursday, June 10, 2010

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
  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

Sunday, June 6, 2010

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:

   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.