I spent something like 2 hours today trying to figure out how a particular user had access to one of my databases. It’s one of the strangest things I’ve ever seen while playing with SQL Server security. Here’s how it plays out:

You have a windows group called [Kenneth-Laptop\Dwarf] with a member called [Kenneth-Laptop\Dopey]. You grant Dwarf SELECT permissions on a database.

CREATE LOGIN [Kenneth-Laptop\Dwarf] FROM WINDOWS;
GO
USE Test;
GO
CREATE USER [Kenneth-Laptop\Dwarf] FROM LOGIN [Kenneth-Laptop\Dwarf];
GRANT SELECT TO [Kenneth-Laptop\Dwarf];

Dopey can now query a table in the Test database. Some time later you remove the login for Dwarf.

DROP LOGIN [Kenneth-Laptop\Dwarf];

So at this point Dwarf is an orphaned user in the Test database and Dopey doesn’t have any direct access. And would you believe Dopey still has SELECT access? For some reason even though the group is orphaned it’s still active.

The takeaway here (aside from an odd security trick) is that if you are removing a server principal (login) you need to remove all of the associated database principals (users).

Filed under: Microsoft SQL Server, Security, SQLServerPedia Syndication Tagged: database permissions, microsoft sql server, security, server permissions