June 6, 2017 at 6:53 am
This is an error which seems to happen a lot but I cannot really get an answer
We have a user on a DB where he has db_owner role, procedures are written with WITH EXECUTE AS in the procedures. The procedures do selects from other databases where this user also has db_owner role. We have a few environments setup like this, two of them on the same server, so they use the same server username.
But on the one set of DBs we get this error, on the other we do not. We have even deleted and recreated the user, this does not work. But the procedures continue to work on the one environment. We cannot find anything as such.
To make it even weirder we deployed to a clients server, they have an old procedure that uses the same user for the WITH EXECUTE AS, this procedure works, but any procedure we add gets this error.
Does anyone have any ideas what to look for or to try. I am quite desperate at this time
June 6, 2017 at 8:35 am
I'm not completely sure what your setup is. You've written some ambiguous pronouns in your description. Can you set up a repro at all? what if you leave the existing user/login (login is the server level) and create a new one. Does this allow the procedures to work?
June 7, 2017 at 1:17 am
Sorry about that, was a bit of a rush.
We have a DB(call it DB2) where 3rd parties sign in with views pointing to other databases(call it DB1) through synonyms. We have a SuperUser that has db owner access to both databases, we hide the user and use it in Stored procedures
ALTER PROCEDURE [dbo].[StoredProcedure]
WITH EXECUTE AS 'SuperUser'
In the Procedure we have a select statement that gets data from DB1 using synonyms
The I grant the 3rd Party user execute permissions on the Procedure
When this user or even the sysadmin tries executing the procedure we get
But If I login as the Superuser and run the select in the procedure we get results
June 7, 2017 at 2:13 am
What is frustrating is in one environment it is working perfectly, in the others not. We have tried creating a new Super User from scratch(changing the procedure accordingly), this even does not work
June 7, 2017 at 2:50 am
All Databases have the same owner. I have even tried DB_CHAINING. We do use this a bit at clients as well.. As stated before the SuperUser is db_owner(note db_owner role, not the owner of the database) of both databases
June 7, 2017 at 4:44 am
Hi John
I am not sure I am following everything, should we be enabling guest access? Guest statuses on all userdatabases are 0
Our DBs have DB Chaining on as per this query
SELECT [name] AS [Database], [is_db_chaining_on]
FROM [sys].databases
ORDER BY [name];
But this is not switched on
SELECT [name], value
FROM [sys].configurations
WHERE [name] = 'cross db ownership chaining';
The Database ownership is the same on both databases, down to object level.
What is confusing is that the select statement cross database works fine, once once you have it inside the procedure with the WITH EXECUTE AS it fails with the error
June 7, 2017 at 5:11 am
Taken from Microsoft's EXECUTE AS page:
To specify EXECUTE AS on a login, the caller must have IMPERSONATE permission on the specified login name and must not be denied the IMPERSONATE ANY LOGIN permission. To specify EXECUTE AS on a database user, the caller must have IMPERSONATE permissions on the specified user name. When EXECUTE AS CALLER is specified, IMPERSONATE permissions are not required.
Are those conditions met on your server? Does the SuperUser user have the same SID in both databases?
John
June 7, 2017 at 5:35 am
Taken from Microsoft's EXECUTE AS page:
To specify EXECUTE AS on a login, the caller must have IMPERSONATE permission on the specified login name and must not be denied the IMPERSONATE ANY LOGIN permission. To specify EXECUTE AS on a database user, the caller must have IMPERSONATE permissions on the specified user name. When EXECUTE AS CALLER is specified, IMPERSONATE permissions are not required.
We are using
ALTER PROCEDURE [dbo].[StoredProcedure]
WITH EXECUTE AS 'SuperUser'
So this seems to apply to what you said above, so Impersonate permissions are not required?
Are those conditions met on your server? Does the SuperUser user have the same SID in both databases?
Ran this query on the username, got this result on d.sid, so it looks fine
0xF342055768B2D54FB4D4E33DE52BF3B7
0xF342055768B2D54FB4D4E33DE52BF3B7
June 7, 2017 at 5:46 am
Andre 425568 - Wednesday, June 7, 2017 5:35 AMSo this seems to apply to what you said above, so Impersonate permissions are not required?
That's not how I interpreted it. I think the caller of the stored procedure needs IMPERSONATE permission on SuperUser.
John
June 7, 2017 at 5:51 am
Added to that if I run this
select d.name, d.sid, s.name, s.sid
from sys.database_principals d
full join sys.server_principals s
on d.principal_id = s.principal_id
where d.name = 'SuperUser'
Columns 3 and 4 are nulls. Is this the root cause of the problems, I am not sure how to interpret this, can someone explain this?
June 7, 2017 at 5:54 am
When EXECUTE AS CALLER is specified, IMPERSONATE permissions are not required. -- This is why I wonder if you need impersonate access added to the Procedure
June 7, 2017 at 5:56 am
When EXECUTE AS CALLEREXECUTE AS CALLER is specified, IMPERSONATEIMPERSONATE permissions are not required. -- This is why I wonder if you need impersonate access added to the Procedure
EXECUTE AS CALLER isn't specified. EXECUTE AS 'SuperUser' is.
Added to that if I run this
select d.name, d.sid, s.name, s.sid
from sys.database_principals d
full join sys.server_principals s
on d.principal_id = s.principal_id
where d.name = 'SuperUser'Columns 3 and 4 are nulls. Is this the root cause of the problems, I am not sure how to interpret this, can someone explain this?
No, that's not the root cause. When you join database_principals to server_principals, you should join on sid.
John
June 7, 2017 at 6:02 am
Will give it a try
Viewing 15 posts - 1 through 15 (of 25 total)
You must be logged in to reply to this topic. Login to reply