Partially Contained Databases: Permissions used by contained user vs login with same name

I recently gave a webcast on Partially Contained Databases and one of the attendees had an interesting question:

Hi Steve,

About Contained database, let’s say I have the same username defined at the SQL Server level and at the contained database level.
The 2 users have different roles on the database which also means different rights.
What should I expect in term of rights when I connect to the database using that username ?

Regards,
Cedric

So I said I would try it out. It didn’t work out as expected. The database role got lost during the conversion from non contained to partially contained. I reproduced it like three times and three times it failed until I tried it on another instance on another machine and…it worked just fine(…)

So, a small example script:

Let’s start with creating a simple user database called PCDB (Partially Contained Database)

– Create a Windows login “Heidi”
CREATE LOGIN [KOHERA-KADHeidi] FROM WINDOWS WITH DEFAULT_DATABASE=[PCDB], DEFAULT_LANGUAGE=[us_english]
GO

USE [PCDB]
GO

– Create a sql user with password “Heidi”
CREATE USER [Heidi] WITH PASSWORD=N’?ĹÌÔ´?Ù§9?p?T#ó÷û?ÿP7+£2_s_×’, DEFAULT_SCHEMA=[dbo]
GO

– Assign the db_denydatareader db role to “Heidi”
ALTER AUTHORIZATION ON SCHEMA::[db_denydatareader] TO [Heidi]
GO

— Let’s create a table for some testing
create table MyTable(ID tinyint, Name varchar(12))
insert into MyTable values (1, ‘Heidi’);

I then logged in as “Heidi” (SQL user with password) and executed a simple select statement:

Msg 229, Level 14, State 5, Line 1
The SELECT permission was denied on the object ‘MyTable’,database ‘PCDB’,schema ‘dbo’.

So that seems to work. User “Heidi” was assigned the db_denydatareader db role

So now let’s start SSMS as Windows user “Heidi” and authenticate with user “Heidi” in the master db and try to execute an insert stament

insert into MyTable values (2, ‘Heidi’);

Msg 229, Level 14, State 5, Line 1
The INSERT permission was denied on the object ‘MyTable’, database ‘PCDB’, schema ‘dbo’.

Conclusion:
There is no mix-up on the db roles when authentication on instance level or database level. The permissions based on the db roles are respected.

 

 

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>