Voice of the DBA

Tag: syndicated

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
Be Extra Careful When Packing

Over the last couple of years I’ve gotten myself down to two small, carry-on bags for most of my trips. It has allowed me to save time in and out of the airport, and cut down on a lot of hassles from frequent trips (I flew on 18 round trips last year).

One of the ways I’ve done this is with a couple dedicated bag: one for clothes/shoes and one for the laptop and supplies. I don’t ever really unpack them, and I just move clothes in and out when I go. That always has me a little nervous as I am counting on things to be in the bags, but it’s worked for a dozen trips lately because I keep spares of all my supplies in the bags, replacing them as needed, but not using them for other purposes.

However that can come back to bite you. I did have a trip last year where I left my presentation remote at home since I’d been using it to practice and hadn’t put it back in my bag before I left. Fortunately that’s a minor inconvenience and not a major hassle.

I leave for the UK on Saturday for my annual visit to the Red Gate office in Cambridge, and I need to pack for a week. I’m hoping to slip in enough clothes to go with just my two bags, but we’ll see. I’ll also use this chance to double check I have everything packed I need since replacing stuff in another country is less than exciting to me.

April 12, 2012
Hashing Collisions
One of the problems with hashing is that you can have collisions from values that are not very similar. This means that if you are using hashing as a way to identify similar values, you need to make further checks with the original data after the hash matches are gathered.

This post will show a few examples of the collisions that can occur if you use the CHECKSUM() or BINARY_CHECKSUM() functions.

If we examine this code:
```
-- Checksum
declare 
  @i varchar(200)
, @j varchar(200);

select @i = 'LE';
select @j = 'AAAAAAAAAAAAAAAALE';

select 
  Plaintext = @i
, checksum = CHECKSUM(@i)
UNION ALL 
SELECT
  Plaintext = @j
, checksum = CHECKSUM(@j);
GO
```
This returns a result like this:

Note that these two values are the same as far as the checksum hash goes.

If we switch to BINARY_CHECKSUM(), we can get similar results.
```
-- binary_checksum is no better
declare 
  @i varchar(200)
, @j varchar(200)
, @k varchar(200);


select @i = 'LE'
select @j = 'Ou'
select @k = 'MU'

select 
  Plaintext = @i
, BINARY_CHECKSUM(@i)
UNION ALL 
SELECT
  Plaintext = @j
, BINARY_CHECKSUM(@j)
UNION ALL 
SELECT
  Plaintext = @k
, BINARY_CHECKSUM(@k)
GO
```
While these two functions can be useful, you do have to be careful with the results. A matching hash from these functions does not mean that the source data is the same.
April 11, 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