I use impersonation all the time to let me see additional permissions information I can only get that way and, sometimes to help me see what permissions error someone is getting.

For those of you who aren’t aware, with the right permissions you can impersonate another principal (server or database) using the command EXECUTE AS

-- Impersonate a server principal
EXECUTE AS LOGIN = 'ImpersonateServerPrincipal'
-- Impersonate a database principal
EXECUTE AS USER = 'ImpersonateDatabasePrincipal'

Once you have run one or the other commands you will act like you are that principal until you use the REVERT command to change back to your original credentials.

Sounds really useful right? It is. I frequently use it to find AD information and I also use it to look at sys.fn_my_permissions as a quick way to get a users effective permissions. Either for the database or a specific object. Works great! Until it doesn’t. Well, it was technically working, I just got some really confusing results.

The other day I ran across an interesting problem. A user was logging in but didn’t have access to a database they were certain they used to access to. We checked and there they were. Not only was there a database principal (a user) but it was a member of db_owner. But still no go. The user could not connect. I went to the database and impersonated them and then checked sys.fn_my_permissions. They were definitely a member of db_owner. I tested and yes, I could read the tables they needed, and yes they could execute the stored procedures they needed to execute. So what was wrong?

Well after several minutes of head scratching I realized I’d made a big mistake. One I consider a cardinal mistake when dealing with SQL Server security. I’d been thinking that just because the names were the same the server and database principals were related. One of the major points I make in my SQL Server Security for Everyone session is that the server and database principals are joined by the SID and if you forget that and start thinking that just because the names are the same that the principals are the same you can get very very confused. (And I was right wasn’t I?)

It turns out the database principal was orphaned (no longer associated with the server principal). When I impersonated the USER I was only impersonating the database principal. I wasn’t seeing anything to do with the server principal or even if there was one. On the other hand when the user logged in they were connecting as the server principal, but that wasn’t associated with the correct database principal so they had no connection. Now it’s an easy fix once you realize what’s going on.

ALTER USER [TestUser] WITH LOGIN = [TestUser]

And if you want to play around with it yourself here is an easy way to set the situation up.

USE master
GO
CREATE LOGIN [TestUser] with password = 'TestUser'
GO
USE UserDB
GO
CREATE USER [TestUser] FOR LOGIN [TestUser];
GO
USE master
GO
DROP LOGIN [TestUser]
GO
CREATE LOGIN [TestUser] with password = 'TestUser'
GO

And here you can see that the SIDs no longer match up.

USE UserDB
GO
SELECT 'Server Principal' AS PrincipalType,
Name, SID
FROM sys.server_principals
WHERE name = 'Testuser'
UNION ALL
SELECT 'Database Principal' AS PrincipalType,
Name, SID
FROM sys.database_principals
WHERE name = 'Testuser'

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