Voice of the DBA

Tag: sql server

Creating a User without a Login – Contained Databases
In SQL Server 2012, we have a new feature: partially contained databases. In a previous post, I showed how to enable this, and this post will look at one of the advantages of contained databases: users without logins.

Contained Users

One of the problems in non-contained databases is the fact that when a database is moved or restored, the login mapping to the user in the database doesn’t always transfer cleanly. Microsoft has sp_help_revlogin and sp_change_users_login to help fix this, but in a DR situation, or in a crisis, this may not work. It’s also a hassle.

Contained users help fix this. They are users that exist within the database, and do not require a login mapping. The server level authentication will transfer to a database level authentication, if the database has the partial containment option set.

To create a contained user, you can use the GUI, or T-SQL, both of which are easy and I’ll show them below:

SSMS Contained User

If you right click the Users folder (under Security) in a database, you can select the New User option.

When this appears, you can then use the drop down to select a User with a Password option for a SQL Server user that is contained inside a database.

The traditional user is a user with a login. Here’s the dialog from SSMS 2008, with no option for a user without a password.

Back to 2012, I can enter a user name and password, and then I have a user in my database.

The process for a Windows user (again, without a login) is similar. I can select a “Windows User” and then select the ellipsis by the User name and search for an AD user.

This looks the same when I accept a user

I can set a default schema here, and a language, but I don’t need the login.

T-SQL

The process with T-SQL is the same. The code for the CREATE USER command is simple:
```
create user Billy with password = 'Billy2Goat$Gruff'
;
```
If I want a Windows user, I can do this:
```
CREATE USER [DKRSQL2012\Andy]
GO
```
Note that this is domain\user syntax. Some AD tools allow the user@domain syntax, but this isn’t allowed in SQL Server 2012 for the CREATE USER command.

You can replace the user name with a group at the database level, and the syntax is the same.

Summary

That’s it. It’s simple, and in another post, I’ll look at authentication.
April 16, 2012
Key Storage
Hopefully your digital key storage is more organized and secure than this.

One of the issues with encryption, perhaps the biggest issue, is the management of the keys that protect the encrypted data. I have been an advocate of keeping the backup of the keys far away from the backup of the encrypted data.I usually want them on separate media, or a separate tape, just so that a loss of my backup of the data (or the data itself), doesn’t include the key.

However this presents a problem in a DR situation, especially over time. If I make a backup, and lose my server in a year, can I easily find the copies of the asymmetric keys or certificates? Can I easily match up the proper key with the encryption if I rotate keys periodically? There hasn’t been a great solution I’ve seen to solving this issue.

Recently I saw a talk on security, and the speaker mentioned they kept copies of their certificates on the backup tape with the backup of the data. This person felt that since a password was needed for the certificate, that this was secure enough. Perhaps, but you still have the problem of securing that password over time as well. This week, I wanted to ask those of you that use encryption, how do you handle the issue.

Would you store a secure asymmetric key protected with a password on your backup drive or tape?

If so, then how do you handle the security of the password? If not, then what other solution do you have? I know key management is a struggle in many organizations, but if you have something that works for you, let us know how it works.

Steve Jones

The Voice of the DBA Podcasts

We publish three versions of the podcast each day for you to enjoy.
- Watch the Windows Media Podcast – 18.3MB WMV
- Watch the iPod Video Podcast – 18.9MB MP4
- Watch the MP3 Audio Podcast – 3.1MB MP3
April 13, 2012
Enabling Contained Databases in SQL Server 2012
One of the new features in SQL Server 2012 is the Partially Contained Database feature. I gave a talk on this recently, and I’m looking forward to seeing where this might go in the future.

This post looks at how you can enable containment in SQL Server 2012. It’s a very simple process, in a couple steps, and I’ll show you both with the GUI and with T-SQL.

Enable the Instance setting

There are two levels to enable containment. The first is at the instance level. If you get the server properties for your instance, and look at the advanced tab, you’ll see this:

You can use the drop down to select the setting you want (true = enabled), but please don’t click the OK button. Always, always, always click the “Script” button. This allows you to see the exact code being run, and then you can also use this in your documentation for change control. Even if this is a development server, get in the habit of scripting things.

Once you click the script button, you’ll get this T-SQL:
```
EXEC sys.sp_configure N'contained database authentication', N'1'
GO
RECONFIGURE WITH OVERRIDE
GO
```
This will enable containment for the instance and you’ll be halfway there.

Database Containment

The database itself also has a containment setting. In this case, you can look at the properties for the database, on the options tab.

If you look near the top, you have the collation drop down, the Recovery mode, the compatibility model, and then containment is new in 2012. You have “None” and “Partial” available, and clicking Partial will enable containment in 2012. Again, please don’t click OK, but click script.

The code will appear as below:
```
USE [master]
GO
ALTER DATABASE [cdb2] SET CONTAINMENT = PARTIAL WITH NO_WAIT
GO
```
You can also set this value when you create a database:
```
-- create db with containment
CREATE DATABASE cdb2
 CONTAINMENT = PARTIAL
```
That’s it.

These two items together will enable containment on a database, and then you can work with contained users, something I’ll talk about in another post.
April 9, 2012
SQL Server 2012 Beta Exams
I signed up for a few of the SQL Server 2012 Beta certification exams, and have been going through them over the last few days. The exams I signed up for are:
I took the first one on Monday, and the second on Wednesday. I have the other two scheduled for Friday and next Wednesday.

I can’t really comment on what was in the exam, but I did see some evolutions of the testing software, which was nice. I like a few of the changes, and they should help people demonstrate a few more skills than the old multiple-guess exams. There are still multiple choice questions, but some new ways to ask someone to show knowledge.

I’m not really worried about being certified, but I do like to see how the certification process works, I might write some stuff to help people (I’ve worked on three cert books in the past) and most importantly, I get a rough idea of what I know on the exams. They’ll ask me a few things I might not have looked at in depth, and I certainly found a few holes in my knowledge over the last few exams.

If you have the chance to take a beta, I’d encourage you. They’re free, and the time they take can be a valuable aid in helping plan your future learning.
April 5, 2012