Please close the door behind you…

Please close the door behind you…

Sometimes, you come across a situation that you know you should know the answer to, but can\’t actually put your finger on. But something tells you that its fundamental knowledge. And something else then starts kicking you until you re-learn that fundamental knowledge.

Like the other day, when someone asked me \”If someone is only a member of a Windows group that has access to Database A, but they are defined as a User in Database B, can they access data in Database B ?\”

And the answer is \”yes, they can\” (but I\’m guessing that they probably shouldn\’t be able to). And I had trouble remembering that, since it\’s not something I get asked very often 🙂

Presumably, this happens in the development / early production stages, when nervous developers insist on having \’dbo\’ permissions to their baby database in their own right (rather than having a proper NT group for support), and then their login gets dropped when the service has been in for a while and is stable.

I\’m sure this happens quite a lot (and from a bit of digging, a bit more often than I originally would have thought), and those thoughtful guys at Microsoft even put a dialog box up when you delete a login advising you that you still need to delete any database user associated with the Windows login. What\’s not 100% clear from that message it that database permissions for the user will remain, and the end-user can still get access to the data even if their login does not explicitly exist on the SQL server.

Is this a bit of a security hole ? Perhaps it is. But then again (as the helpful message intimates), SQL Server cannot take arbitrary decisions about who owns what object in the database (or indeed whether any object should be removed). It\’s down to the DBA to make those decisions according to each circumstance, and with careful consultation with the end-users – you don\’t really want to drop a table that other people or services rely on day-to-day….

So, next time you get asked to drop a login for a Windows user or NT Group, remember to tidy up all the permissions in all the databases that the login had access to before you get your next latte / mocha / cold one. It\’ll save embarrasing questions about unauthorised data access later on, and it\’ll stop things kicking you in the head when you can\’t remember fundamental knowledge. Which is a win on both counts !

Certainly, from now on I\’ll be double checking that all permissions are revoked when a login is dropped. Especially on the HR and Financial systems.

Back soon !