As one door closes, another one opens.

As one door closes, another one opens.

Further to yesterday\’s post about remembering to tidy up database users, I thought I should mention something else.

It is entirely possible to create a user in a database that is associated with a Windows login WITHOUT adding the Windows login into master. And this DOES look a bit like a security hole in SQL Server (or possibly something that DBAs should be aware of, just in case).

Anyone who has \’dbo\’ rights to a database can create a user in that database, as we know, but they don\’t have rights to create logins in SQL Server. There is, however, a way of circumventing this.And here\’s how to do it….

In SMSS object explorer, choose any non-system database you like, and expand \’Security\’ and then \’Users\’. Bring up the dialog box to create a new user….

In the UserName field, pick a valid Windows user (perhaps someone you know). Or, if you\’re into keeping database users and logins distinct, type your own style of user name in the box.

OK, moving on to the LoginName field, here you would normally choose the […] box to pick and existing login to associate with your sparkly new database user.

You don\’t have to, though. You can just type in any valid Windows login. And that\’s the hole.

When you create the user, SQL will query Active Directory for the SID of the domain user that you entered. If it\’s valid, the new database user is created as normal, and the correct entry is added sysusers.

If you check the properties of the newly created user in SMSS, you\’ll see that there is a mapping between the UserName and the LoginName as normal (please excuse the crappy redaction – there\’s a recession on):

…even though there is no corresponding login in master (again, sorry for the redaction):

If you go back to the database you created your new user in, and SELECT * FROM sysusers, you\’ll see your Windows login in there, as well as the SID that SQL helpfully picked up from AD.

Going back to the post the other day, this now means that if the Windows user you used is a member of a group that has access to your server (but not the database you\’ve just \’created\’ them in), they will be able to access your database as well.

….which looks like a bit of a loophole to me…

So, I guess the morals of this story are \”Be careful who gets \’dbo\’ rights to any database\”, and \”Keep a close eye on all your database users AT ALL TIMES\”.

But I suppose you\’re already thinking of writing a script or policy that would remove any explicit database users who don\’t have a corresponding login. 🙂

Good luck with that….

Back soon !