I have two databases on the same server, lets call them DB1
and DB2
. There is a login on the server called MyUser
, and both databases have a user setup from that login, also called MyUser
[edit: and are not orphaned - just double-checked]. The following works fine in DB2
, but not in DB1
:
execute as user = 'MyUser'
execute as login = 'MyUser'
When I run this in DB1
, I get:
Cannot execute as the server principal because the principal "MyUser" does not exist, this type of principal cannot be impersonated, or you do not have permission.
As far as I can tell both MyUser
users belong to the same roles on their respective databases and those roles have the same permissions that they grant.
To try and figure this out I've run the first execute as
and then selecting some further information as follows, and this is where it gets interesting:
execute as user = 'MyUser'
SELECT *
FROM sys.login_token
WHERE principal_id > 0;
SELECT *
FROM sys.user_token
WHERE principal_id > 0;
Now, the output I get for these is as follows. For DB1
(the problem database) I get (leaving off the pricipal_id
and sid
cols), firstly from sys.login_token
:
name | type | usage |
---|---|---|
MyUser | SQL LOGIN | DENY ONLY |
public | SERVER ROLE | DENY ONLY |
And then from sys.user_token
, on DB1
:
name | type | usage |
---|---|---|
MyUser | SQL USER | GRANT OR DENY |
MyRole | ROLE | GRANT OR DENY |
Now, running the same thing in DB2
, firstly from sys.login_token
:
name | type | usage |
---|---|---|
MyUser | SQL LOGIN | GRANT OR DENY |
public | SERVER ROLE | GRANT OR DENY |
sa | SQL LOGIN | AUTHENTICATOR |
And finally from sys.user_token
, on DB2
:
name | type | usage |
---|---|---|
MyUser | SQL USER | GRANT OR DENY |
MyRole | ROLE | GRANT OR DENY |
dbo | SQL USER | AUTHENTICATOR |
dbo_owner | ROLE | AUTHENTICATOR |
I can't see anything different between the dbo
database User and dbo_owner
Role objects between the two databases, nor how the sa
server login applies at all differently to the two databases, and have no idea how they contribute to these "tokens".
Where should/could I look next to solve this puzzle? (As this is in a highly accessed PROD environment I don't want to make any unnecessary changes)
Any help much appreciated!
USE DB1; IF EXISTS ( SELECT 1 FROM sys.database_principals a LEFT JOIN sys.server_principals b ON a.sid = b.sid WHERE a.name = 'MyUser' AND b.sid IS NULL); PRINT 'MyUser is orphaned.'
user_token
output I seedbo_owner
role seems to be missing. Can you run these two onDB1
and see?USE DB1; GO EXEC sp_addrolemember 'db_owner', 'MyRole'; GO EXEC sp_addrolemember 'MyRole', 'MyUser'; GO
DB2
,MyRole
is not a member ofdb_owner
, and in bothDB1
andDB2
,MyUser
is already a member ofMyRole
. I checked withSELECT DP1.name AS DatabaseRoleName, isnull (DP2.name, 'No members') AS DatabaseUserName FROM sys.database_role_members AS DRM RIGHT OUTER JOIN sys.database_principals AS DP1 ON DRM.role_principal_id = DP1.principal_id LEFT OUTER JOIN sys.database_principals AS DP2 ON DRM.member_principal_id = DP2.principal_id WHERE DP1.type = 'R' ORDER BY DP1.name;