June 7, 2017 at 6:05 am
for a stored procedure, the EXECUTE AS must name to a USER in the database
it cannot use a LOGIN who is not an EXPLICIT USER in the database.
If you simply create the user for your superuser login,and grant that new user db_owner, it will work.
a sysadmin typically does not have a user in any database, since permission checking is ignored for permissions.
Lowell
June 7, 2017 at 6:16 am
Hi Lowell, did you read through the whole story from the beginning, Think this is what I have done. It is working perfectly on the one environment, on another on the same sql instance it is not
June 7, 2017 at 6:26 am
I am a bit skeptical about impersonate permissions as they are on user level. Is impersonation not already built in in die stored procedure with the WITH EXECUTE AS?
June 8, 2017 at 3:12 am
Anyone have any specifics I can check for?
In summary
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 on DB2 and run the select in the procedure we get results
Further a sysadmin users owns both databases and all database objects. We even tried DB chaining, switching it on and off(we use this frequently so I know how it works)
As for the advise give we seem to be on the right path though I am not sure what the actual difference is between execute as and impersonate. Is this not very much the same and adding it to the Stored procedure should be sufficient or am I wrong. Using impersonation on the login I am unsure of as it seems risky
The use of guest on the database - is that the issue, it seems to be a risk and we do not have it on any of our databases, and our one environment works even without the guest user.
Just as an extra note. The error Is the the server principal is not able to access the database (this database is DB1). On one of our environments we then setup another DB call it DB3, and it worked and the stored procedure could be executed linking to the same DB1(same superuser etc). This is not the solution as we have client environments where we cannot just recreate DB2.
June 8, 2017 at 3:22 am
I wouldn't use the guest user if you don't have to. The difference between EXECUTE AS and IMPERSONATE, as I understand it, is that the former is a statement, whereas the latter is a permission. You need IMPERSONATE permission on SuperUser to use EXECUTE AS 'SuperUser' - something like this:GRANT IMPERSONATE ON SuperUser TO UserWhoWillCallTheStoredProcedure
I may have misunderstood, but it's worth trying.
John
June 8, 2017 at 3:38 am
Will try, just afraid that will give them access to what SuperUser has access to, so they would be able to actually open the database schema for example is superuser has access to it
June 8, 2017 at 3:44 am
Forgot to add, even sysadmins cannot execute the procedures, am I right it saying sysadmins would always have execute rights even if its not been granted to them, we also then get the same error, so impersonate as such would not make a difference to sysadmins right to run the procedure, they would though pick up the error as well
June 8, 2017 at 4:04 am
I don't know. The best way is to replicate the problem in a different environment and then test. I'm intrigued by this, so if you post some scripts I'll try them out myself.
John
June 13, 2018 at 6:53 am
Hi,
It's most likely too late for you already but might help others with the same problem. I've just struggled with the same situation myself, saw this post and after solving my problem I decided to post what worked with me 🙂
Most part of it is stated in the following article:
Extending Database Impersonation by Using EXECUTE AS
The only thing not said in the above article and that was still missing in my case was that the DB (say DB1) holding the stored procedure which in turn accessed other DBs via impersonation, had a different owner from the master database.
So, after extending the Database Impersonation I changed the ownership of DB1, so that it would match the owner of master DB (sa in my case):
ALTER AUTHORIZATION ON DATABASE::[DB1] TO sa --Owner of Source DB must be the same as master db
And from then on it worked just fine!
You mentioned that you implemented the same situation in different environments, it worked in one and not in the other and you could not say why... Could it be that in the one it worked, the DB owner of DB holding the SP was the same as master db and in the other case it wasn't?
Hope this helps someone!
Note: cross database ownership is off
Cheers,
Etienne
September 17, 2019 at 6:50 am
ALTER DATABASE [DB_NAME()] SET TRUSTWORTHY ON
No one seems to link the property to the ability to EXECUTE AS OWNER, etc. but its critical for impersonation, I think.
February 9, 2021 at 2:12 pm
hi everyone
Is there a solution?
brgds Frank
Viewing 11 posts - 16 through 25 (of 25 total)
You must be logged in to reply to this topic. Login to reply