Category Archives: 12 Hours of SQL Server

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.

 

 

The Fantastic 12 of SQL Server 2012

I’m excited to be a part of the 12 Hours of SQL Server on February, 24th hosted by Microsoft BeLux.

12 webcasts about SQL Server 2012 by 12 speakers both local and international starting at 12:12 PM.

The speakers:

  1. Steve Verschaeve (B) - Partially Contained Databases
  2. Jennifer Stirrup (UK) - Power View from the Data Visualisation Perspective
  3. Frederik Vandeputte (B) - Building your first Analysis Services Tabular BI Semantic model with SQL Server 2012
  4. Pieter Vanhove (B) - Manageability Enhancements of SQL Server 2012
  5. Nico Jacobs (B) - What’s new in Integration Services 2012?
  6. Karel Coenye (B) - Manageability Enhancements of SQL Server 2012
  7. Frederik Vandeputte (B) - Enhance your Analysis Services Tabular BI Semantic to get the most out of Power View
  8. Dandy Weyn (US) - Performance and High-Availability at Scale with SQL Server 2012
  9. Gert Drapers (US) - Database Schema Management & Deployment using SQL Server Data Tools (SSDT)
  10. Cihan Biyikoglu (US) - Inside SQL Azure: Self Governing, Self-Healing, Massively Scalable Database Service in the Cloud
  11. TBD
  12. TBD