sys.fn_my_permissions with a specific user within a group that has permission?

Question

sys.fn_my_permissions throws an error whenever I check for permissions for a specific user who has permissions defined via a group that that user is a member of.

For instance, I have a group, myDomain\UserGroup with EXECUTE (among other permissions) on a stored procedure, sp_Test_Remote. myDomain\UserGroup has a user myDomain\MiniMe.Two.

The following query throws an error:

EXECUTE AS USER = 'myDomain\MiniMe.Two';

SELECT PERMISSION_NAME

FROM fn_my_permissions('sp_REMOTE_Test','OBJECT')

ORDER BY PERMISSION_NAME;

REVERT;

Error: Msg 15517, Level 16, State 1, Line 1

Cannot execute as the database principal because the principal "myDomain\MiniMe.Two" does not exist, this type of principal cannot be impersonated, or you do not have permission.

Is there no ability to check a specific user, unless the user is specifically listed in the database as a login? I would think that MS would have accounted for this checking of a user's permissions when they have permissions defined as part of a group.

Thanks.

Answer 1

The error is not from fn_my_permissions, but from EXECUTE AS USER.

The error message would indicate one of two things. The first is that you don't have IMPERSONATE permission on the user in question, but I assume that you are running as sysadmin, why can rule this out. (But if you only have CONTROL SERVER, it may still matter.)

The other reason would be that SQL Server cannot find the user in the AD for whatever reason. The dot in the name is a little funny. I don't know if this matters. Does it work for "normal" names?

You should indeed be able to impersonate a user who only has access to the server through a group. You can even impersonate users who don't have access to the server at all.

Which version (SELECT @@version) of SQL Server do you have?

---

Erland Sommarskog, SQL Server MVP, [e-mail]

Answer 2

Hallo MineMeTwo,

additional to the suggestions from Erland I suppose that user 'mydomain\miniMe.two' is not available in the database.
Check this first!

Another option for investigations is the NAME of the USER.
Maybe your LOGIN has a different name in the database!

Try it with EXECUTE AS LOGIN = 'mydomain\minime.two'

You'll find more details concerning EXECUTE AS here:
http://msdn.microsoft.com/de-de/library/ms181362.aspx

---

Uwe Ricken

MCITP Database Administrator 2005
MCITP Database Administrator 2008
MCITP Microsoft SQL Server 2008, Database Development

db Berater GmbH
http://www-db-berater.de

Answer 3

I am not running this as sysadmin, although I am running as a dbowner for the database. Can you tell me what permission I might need to get access to AD users for impersonating them?

Yes, the dot in the username works fine. On "sever2" this all works great ("server2" has the users defined as individual Users; the original (error) server has them added only as a group). BTW I tested a this with a user added via a group on "server2" and it throws the same error.

Neither server seems to allow me to impersonate users added only through groups, nor users without access to the server at all.

SQL Server version for both servers is:
Microsoft SQL Server 2008 R2 (SP1) - 10.50.2500.0 (X64)   Jun 17 2011 00:54:03   Copyright (c) Microsoft Corporation  Standard Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)

Thank you!

Answer 4

That's correct: the specific user is not in the database, but has access by an AD group in the database.

EXECUTE AS LOGIN results in the same error message.

Thanks for the suggestions.

Answer 5

Each person who’ll be accessing the view must have an explicit, individual account in the SQL Server database to which the view connects. Members of an Active Directory (AD) group cannot be impersonated. For example, if Jane Smith is a member of the AD group Sales, and her database administrator adds the Sales AD group to the SQL Server database, Jane cannot be impersonated.

Get more details concerning EXECUTE AS and IMPERSONATING here:

 http://msdn.microsoft.com/en-us/library/ms181362.aspx

---

Uwe Ricken

MCITP Database Administrator 2005
MCITP Database Administrator 2008
MCITP Microsoft SQL Server 2008, Database Development

db Berater GmbH
http://www-db-berater.de

Answer 6

Thank you. I figured that must be the case from my testing.

Is there a function to which I can pass an AD username and a database object to determine the user permissions (based upon AD group permissions defined in the database)? Or do I have to be more heavy-handed with my querying: determine the AD group(s) the user has access from and then find the permissions for the group(s)?

Thanks again.

Answer 7

Hallo MiniMeTwo,

I suppose you'll get no chance without adding the dedicated account as Login.
If you do not grant explicit permissions to the dedicated login you won't get the needed information.

One option - but not tested - is a job running by sql agent which fires a stored procedure with the following tasks:

a) add the login (if not exists)
b) check the permissions (execute as login will work than)
c) log the permissions in a dedicated relation where only sysadmins will have access to!
d) drop the login (if it didn't exists in step a)

I think this will/can work but I didn't test it previously!

---

Uwe Ricken

MCITP Database Administrator 2005
MCITP Database Administrator 2008
MCITP Microsoft SQL Server 2008, Database Development

db Berater GmbH
http://www-db-berater.de

Answer 8

Good tip. I'll test that.

Thank you.

Answer 9

This did work out. Very good solution! Thank you very much, Uwe.

Answer 10

I am not running this as sysadmin, although I am running as a dbowner for the database. Can you tell me what permission I might need to get access to AD users for impersonating them?

I think you need to be sysadmin. It's not entirely easy to tell, because the documentation in Books Online is not correct. It says that the user/login must exist in sys.database/server_principals, but I seem to recall that I have reported a case where it was possible to impersonate a login that did not even have access to SQL Server at all - and I was told that it was by design.

I'm not a domain right now, so it is difficult for me to test.

---

Erland Sommarskog, SQL Server MVP, [e-mail]

Answer 11

I think you need to be sysadmin. It's not entirely easy to tell, because the documentation in Books Online is not correct. It says that the user/login must exist in sys.database/server_principals, but I seem to recall that I have reported a case where it was possible to impersonate a login that did not even have access to SQL Server at all - and I was told that it was by design.

I'm not a domain right now, so it is difficult for me to test.

I am now at domain, logged in as 'sa' and I am able to impersonate a Windows user that does not appear in sys.server_principals or sys.database_principals. And I don't think this user can even log in at the server. (I am not able to login myself with Windows auth - and my user appears in sys.server_principals!)

It's not equally simple for me to test with a user that is only db_owner, but it makes sense that you cannot impersonate non-existing users in this case.

---

Erland Sommarskog, SQL Server MVP, [e-mail]