Category Archives: Denali

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

Partially Contained Database – Part 2: Create database, convert non partially contained database and migrate users

Now that we enabled the instance for contained databases, see my post Partially Contained Database – Part 1: Setup, we are ready to create a partially contained database (Partial-CDB) and convert an existing non contained database to a Partial-CDB.

1. Create a Partial-CDB

Create a new user database like you normally do either by using the GUI or Transact-SQL. Notice the Containment type and set it to Partial. There is no fully containment yet in SQL Server 2012. You will have to wait for the next version or migrate to SQL Azure.

Or, doing it using T-SQL:

CREATE DATABASE [PartialCDB] CONTAINMENT = PARTIAL
GO

2. Create a user using the contained database authentication

Partial-CDB lets us choose between a SQL user with password or a Windows user. Let’s go for a SQL user with password as it is specific to a partial-CDB. Notice here that our new user is not linked to a server login which makes it not dependent on the instance. That’s it! All  you have to do is create a user with a password and it’s contained within the database.

Or using T-SQL:

USE [PartialCDB]
GO
CREATE USER [Giselle] WITH PASSWORD=N'xxx'
GO

3. Migrating users

Converting a non-partially contained database to a partially contained database does not mean that the existing database users are automatically migrated as well.

Before we migrate the user(s), let’s have a look at the authentication type of the user(s):

select name, type_desc, authentication_type, authentication_type_desc
from sys.database_principals
where name = 'giselle'

Result:

There is a system extended stored procedure sp_migrate_user_to_contained that takes care of the migration for us.
We will migrate all users using a cursor. I know, we’re not supposed to use cursors but I think it can be done for small administration tasks.

declare @user sysname
declare user_cursor cursor
for select dp.name
from sys.database_principals dp
inner join sys.server_principals sp
on dp.sid = sp.sid
where dp.authentication_type = 1
and sp.is_disabled = 0

open user_cursor
fetch next from user_cursor into @user
while @@FETCH_STATUS = 0
begin
execute sp_migrate_user_to_contained
@username = @user,
@rename = N'keep_name',
@disablelogin = N'disable_login';
fetch next from user_cursor into @user
end
close user_cursor
deallocate user_cursor

Now that the migration is done, let’s check:

select name, type_desc, authentication_type, authentication_type_desc
from sys.database_principals
where name = 'giselle'

Result:

We see that the authentication type has changed from 1 tot 2.

That’s all there is to it!

Partially Contained Databases

SQL Server 2012, still as RC0 at the time of this writing, comes with a new feature called Partially Contained Databases also known as CDB (contained database).

First of all, what does it mean and what is it good for and secondly, why partially?

Let’s have a look at a none contained database to see the disadvantages opposed to a contained database. As you know, moving a database to another instance comes with a few issues such as orphaned users and SQL Server agent jobs which you need to recreate. Wouldn’t it be great if you just take the db, backup and restore or detach and attach or even script it to the new instance and go? Well, that’s the idea of self contained databases.

With contained databases, which you explicit ally need to create, it is no longer necessary to authenticate to the instance using a login in the master database. The idea is to create a database user either choosing a SQL authenticated user or a Windows principal to authenticate directly to the contained database.

Now, why partially? You should know that not everything is contained in SQL Server 2012. The fully contained databases should be for a next release of SQL Server. Worth mentioning that SQL Azure already supports fully contained databases!

To find out what is contained, look at http://technet.microsoft.com/en-us/library/ff929188(SQL.110).aspx

To find out what is not contained, look at http://technet.microsoft.com/en-us/library/ff929118(SQL.110).aspx

For more information on Partially Contained Databases, check out http://technet.microsoft.com/en-us/library/ff929071(SQL.110).aspx

Partially Contained Database – Part 1: Setup

This is the first post in a series of partially contained databases. In part 1 we will discuss the setup of this feature including the authentication across the instance.

1.A. Enable contained databases using SSMS

  1. Server properties
  2. Advanced
  3. Containment: set Enable Contained Databases to True

 

1.B. Enable contained databases using T-SQL

EXEC sys.sp_configure N’contained database authentication’, N’1′
GO
RECONFIGURE WITH OVERRIDE
GO

2. Restart the SQL Server instance

Denali – Import Text Editor settings from Visual Studio

Up until SQL Server 2008 R2, you had to customize the font and background colors manually in SSMS.

SQL Server Denali lets you import your Visual Studio settings such as the custom colors in the Text Editor.
The fact that the Denali SSMS splash screen shows Powered By Visual Studio probably has a lot to do with that.

In Visual Studio go to Options, Environment, Import and Export Settings and copy the location where your settings are stored with the .vssettings extension.

Visual Studio Export Settings

 

Next open SSMS, go to Options, Environment, Import and Export Settings and locate your .vssettings file you copied from Visual Studio using the team settings file option.

SSMS Import Settings