The server principal is not able to access the database under the current security context. URGENT

Question

The server principal is not able to access the database under the current security context. URGENT

Viewing 11 posts - 16 through 25 (of 25 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323504 More actions · Answer 1

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

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Andre 425568 SSCrazy Points: 2314 More actions · Answer 2

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

Andre 425568 SSCrazy Points: 2314 More actions · Answer 3

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?

Andre 425568 SSCrazy Points: 2314 More actions · Answer 4

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

The server principal is not able to access the database under the current security context

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.

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 5

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

Andre 425568 SSCrazy Points: 2314 More actions · Answer 6

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

Andre 425568 SSCrazy Points: 2314 More actions · Answer 7

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

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 8

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

etienne_lopes Grasshopper Points: 23 More actions · Answer 9

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

alocyte 93598 SSC Veteran Points: 228 More actions · Answer 10

ALTER DATABASE [DB_NAME()] SET TRUSTWORTHY ON

https://docs.microsoft.com/en-us/sql/relational-databases/security/trustworthy-database-property?view=sql-server-2017

No one seems to link the property to the ability to EXECUTE AS OWNER, etc. but its critical for impersonation, I think.

frank.kress SSC Veteran Points: 281 More actions · Answer 11

frank.kress

SSC Veteran

Points: 281

February 9, 2021 at 2:12 pm

#3842930

hi everyone

Is there a solution?

brgds Frank