0

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!

8
  • 1
    Did you check if MyUser is orphaned in DB1? 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.'
    – S.D.
    Commented Jan 25 at 7:06
  • Double checked. Definitely not orphaned.
    – monty
    Commented Jan 25 at 21:07
  • Thank you for checking, from your user_token output I see dbo_owner role seems to be missing. Can you run these two on DB1 and see? USE DB1; GO EXEC sp_addrolemember 'db_owner', 'MyRole'; GO EXEC sp_addrolemember 'MyRole', 'MyUser'; GO
    – S.D.
    Commented Jan 26 at 0:26
  • in DB2, MyRole is not a member of db_owner, and in both DB1 and DB2, MyUser is already a member of MyRole. I checked with SELECT 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;
    – monty
    Commented Jan 26 at 4:26
  • Seems to me some miss configuration, try with a clean slate and see if that helps. gist.github.com/dvsrk/d5da6da22c7ffdb867a2130f623c1a1f
    – S.D.
    Commented Jan 26 at 5:39

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Browse other questions tagged or ask your own question.