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
