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

  • 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!

  • 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

  • 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?

  • 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.

  • 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

  • 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

  • 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

  • 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

  • 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

  • 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.

  • hi everyone

    Is there a solution?

     

    brgds Frank

     

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

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