A question came up at work the other day, mostly as a mind game, but you never know it might actually come up at some point. So here is the idea.

• UserA is a member of Group1, Group2 and Group3.
• Group1 is set up as a server principal with a default database of DB1
• Group2 is set up as a server principal with a default database of DB2
• Group3 is set up as a server principal with a default database of DB3

When UserA logs into the instance what database is he connected to?

If you haven’t already guessed, my last two posts have been setup for this one. If you are unsure what NET USER and NET LOCALGROUP are read Adding new user and groups in windows and if you are unsure what I mean by a default database read Default Databases.

Now that we are all on the same page let’s figure this out.

First step is to create the user, all of the groups and add the user to each of them. In a command shell run as administrator run the following script.

NET USER "UserA" "NewPassword" /ADD
NET LOCALGROUP "Group1" /ADD
NET LOCALGROUP "Group2" /ADD
NET LOCALGROUP "Group3" /ADD
NET LOCALGROUP "Group1" "UserA" /ADD
NET LOCALGROUP "Group2" "UserA" /ADD
NET LOCALGROUP "Group3" "UserA" /ADD

Now we run some tests. The first thing I did was to open a copy of SSMS as UserA. To do this hold the shift key down while right clicking on the shortcut.

Then select Run as different user.

Log in as UserA and you now have a copy of SSMS using our test user. The extra work is necessary because you can’t just enter a username and password for a windows connection. Meanwhile our main user can open up another copy of SSMS. (I’m assuming this user will be a sysadmin and can make the changes needed.)

Test 1: Will a login associated with the user will override those associated with the group.

Run this code on the instance of your choice as a sysadmin (or securityadmin will work too).

CREATE LOGIN [yourdomain\Group1] FROM WINDOWS WITH DEFAULT_DATABASE = YourDB1
CREATE LOGIN [yourdomain\Group2] FROM WINDOWS WITH DEFAULT_DATABASE = YourDB2
CREATE LOGIN [yourdomain\Group3] FROM WINDOWS WITH DEFAULT_DATABASE = YourDB3
CREATE LOGIN [yourdomain\UserA] FROM WINDOWS WITH DEFAULT_DATABASE = YourDB4

Now open a query window using the copy of SSMS opened under UserA. Your query will be opened under YourDB4. So that’s answer number 1. The DEFAULT_DATABASE of the user will override any groups. That’s to be expected but it’s always good to check.

Test 2: If I have all three groups as logins which database do I end up in?

Now run this script using your sysadmin connection.

DROP LOGIN [yourdomain\Group1]

Now open a new query window using the copy of SSMS opened under UserA. The result is going to depend on what databases you set as the defaults. In my case it was always first database in alphabetical order by database name.

I want to put in a couple of caveats here. I ran this experiment several times in several ways to make sure it wasn’t database id, sid, or something like that. I also ran it using AD (active directory) groups and users rather than local windows groups and users. Every time it came up the same way. The first database in alphabetical order. All that being said there could be other factors that I missed. I certainly didn’t test layered groups. Where UserA belongs to Group1, Group1 belongs to Group2 etc. Also I’m told that there may be different priorities in AD groups and there are certainly different types of AD groups and users.

So there you go. Given multiple groups with different default databases it will be the first database in alphabetical order.

Probably 🙂

Filed under: Microsoft SQL Server, Security, Settings Tagged: database settings, default settings, microsoft sql server, security