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

Copy query from an Excel worksheet

I recently ran a query, copy and pasted the results in an Excel Workbook. The data in the first worksheet and the query in the next Worksheet for reuse in case I need it again.

So today I had to run that query again…copied the query from the worksheet and got an error:

Msg 102, Level 15, State 1, Line 1Incorrect syntax near ‘ ‘.

I double clicked the error and the following line got highlighted:

DECLARE @country_id CHAR(2)

Apparently there is an error at the end of char(2). I removed that line so the following line became the first line of the code:

SELECT @country_id = ‘BE’

Tried to parse once more (I expected another error because the declaration was missing), the same error occurred.  I knew it had something to do with the copy from Excel. After all, Excel is not the right type of application to store queries.

I tried to paste the query in Notepad so all formatting would be gone and it still wouldn’t work.
Paste and copy once more from within Gmail didn’t work either until I actually send an e-mail to myself with the query in the body of the mail. I copied the query to SSMS and it finally worked!!

 

TechDays 2012: My top 5 sessions to attend

TechDays 2012 is coming closer  (14-16 February) and I look forward to attend as a MEET member. This is a list of the top 5 sessions I will attend:

  1. How to achieve a more agile and dynamic IT environment by Ward Ralston
    This is the IT Pro opening session. The other option is for Devs about Windows Azure by Scott Guthrie
    I’m an IT Pro so easy choice.
  2. 12 reasons to love SQL Server 2012 by Dandy Weyn
    The only SQL Server session (but then again, we had SQL Server Days about 2 months ago).
    Easy here as well as I’m a SQL Server guy and I like Dandy Weyn very much.
  3. 10 Deadly Sins of Administrators about Windows Security by Paula Januszkiewicz
    Well, fellow DBAs will know that understanding Windows Server is a must so I’m curious on this one.
  4. The Future of C# and Visual Basic by Bart De Smet
    Ok, I’m not a developer and probably won’t understand halve of it but you got to see Bart De Smet presenting! He really is a genius and probably the smartest guy I have ever met. He would fit right into the Big Bang Theory!
  5. ScottGu Unplugged by Scott Guthrie
    Well, you haven’t been at TechDays if you haven’t seen Scott Guthrie, have you?

See you at TechDays 2012!!!

Transaction rollback in progress without an end

This is a quick post, I hope it’s useful to someone.

I was dealing with a rollback/kill process which I couldn’t kill.
An sp_whoisactive (by @adammachanic) showed a killed/rollback for more then 29 days!
A simple kill of spid 75 didn’t work.

 

This was the result of kill 75:

SPID 75: transaction rollback in progress. Estimated rollback completion: 0%. Estimated time remaining: 0 seconds.


The result of sp_whoisactive showed me it was an xp_cmdshell command so I knew I had to look at the processes and try to kill it from there. Of course, the Task Manager didn’t give me any details so I fired up Process Explorer from sysinternals and this is what I got:

 

So all I had to do was kill the process from within Process Explorer and voila.

(SQL Server) Consultant To Do list for new client

This list might come in handy for consultants (or anyone else). Feel free to comment so I can update the list.
This is a list I compiled over the years and it has saved me some time. I hope you find it useful.

  1. Physical access to the building. At day one, someone usually picks you up at the gate. Try to get a badge for the following days.
  2. Make sure the client has a domain account ready for you. Most clients don’t want you to connect your laptop to their network. When you can use your own laptop, make sure your anti-virus software is up to date.
  3. Ask for Internet access which you need for e-mail to stay in touch with your colleagues and to search online in case of problems.
  4. Access to the root of the C: drive. I had multiple occasions where I really needed access to the root and lost quite a bit of time because of restrictions.
  5. Access to all necessary SQL Server instances .
  6. Ask for the proper database role on the instances.
  7. Ask for RDP access when necessarily.
  8. Printer mapping.
  9. Where are the toilets, vending machine(s), restaurant(if any) and most important, the coffee machine?
  10. All necessary software must be installed (eg: MS Office, SSMS, …)
  11. What are the local helpdesk procedures?
  12. Timesheet procedure. Most clients have their own timesheet tool (even for consultants).
  13. Name of the project(if any). Might be important to name a project in Visual Studio.
  14. Which is the company standard for naming conventions (servers, databases, database objects, …)
  15. Is there a control versioning tool?
  16. Parking space (any know security issues?)
  17. Company policies (use of Internet, …)
  18. Security (SQL Logins, passwords,…)
  19. When to use your badge (toilet, lunch, smoking break)
  20. How much time do you have for lunch?
  21. Is the use of cell phones permitted?
  22. Is there any documentation of the project?
  23. Report regurlary to the client.

 

I keep a Lacie iamaKey with me all the time with the following utilities on it:

  1. CPU-Z (free)
  2. EventSentry
  3. PowerGUI (free)
  4. Remote Desktop Manager (free)
  5. SQL Sentry Plan Explorer (free)
  6. SSMS ToolsPack (free)
  7. TerraCopy (free)
  8. Sysinternals (free)
  9. RedGate SQLToolbelt
  10. SQLQueryStress (free)
  11. SQLIOSim (free)
  12. SQL Server Internals viewer (free)
  13. RML Utilities (free)
  14. SQLNexus (free)
  15. PAL (free)
  16. PSSDiag-SQLDiag Congiguration Manager (free)
  17. Hex Editor (free)

 

Also in my bag I keep:

  1. I wireless mouse (I prefer my own mouse)
  2. A descent UTP CAT-5 cable (aka network cable)
  3. USB cables
  4. Power Adapter for cell phone
  5. USB memory sticks
  6. External hard disk (includes a SQL script repository)
  7. A Swiss army knife
  8. Business cards
  9. Painkillers
  10. A Kindle
  11. A Moleskine notebook + Pens

My mind to your mind