Post reply

view in database1; acces in database2 without account in database1

  • December 15, 2017 at 12:35 pm

    #402758

    Hi, I created view in database1 based on tables from database2 and I gave select permission to user, which have access only to database1. This user can't take data from view because he don't have account in database2. How can I solve this issue? I don't want create account in database2.
    Thx in advance.

  • December 15, 2017 at 1:54 pm

    #1972348

    Does it have to be a view?  If not, you could create a stored procedure that uses the EXECUTE AS clause to run the query as a different user that does already have access to database 2:
    https://docs.microsoft.com/en-us/sql/t-sql/statements/execute-as-clause-transact-sql

    You will need to sign the stored proc with a certificate though:
    https://docs.microsoft.com/en-us/sql/relational-databases/tutorial-signing-stored-procedures-with-a-certificate

  • December 15, 2017 at 2:03 pm

    #1972350

    Thanks a lot for your answer! 
    I prefer a view because I'm not an admin of this database so for every new refreshing table I will have to ask admin.

  • December 15, 2017 at 3:50 pm

    #1972356

    tomaszredwanz - Friday, December 15, 2017 2:03 PM

    Thanks a lot for your answer! 
    I prefer a view because I'm not an admin of this database so for every new refreshing table I will have to ask admin.

    It seems it would be so much easier to create the account in the second database. Being you don't want to do that or do a signed stored procedure, I think your only other option would be to expose a lot of security risks. It doesn't seem right to do that when someone else (the admin) has to deal with the consequences of that and will be the one dealing with potential issues. But you can talk to the admin about Cross database ownership chaining and enabling the guest account. These are off by default and not recommended to be enabled by Microsoft. Since you aren't an admin, you can't make the changes. Here is the article you would want to discuss with the admin:
    Enabling Cross-Database Access in SQL Server

    It's not a good idea.

    Sue

  • December 15, 2017 at 4:28 pm

    #1972357

    tomaszredwanz - Friday, December 15, 2017 2:03 PM

    Thanks a lot for your answer! 
    I prefer a view because I'm not an admin of this database so for every new refreshing table I will have to ask admin.

    There's a reason for such limitations.  It keeps non-admins from making such unauthorized decisions about security.  Talk with the admin.  If you don't have a good enough reason and they say "No", then you just have to live with that because, ultimately, you're not the one that will take the heat for security.  The DBAs are.

    Remember that the first step to take in the culture of "DevOps" is to communicate.  You're trying to skip that very important step.  Also, you shouldn't be setting up individual users.  You should, or rather, your DBA should be setting up user groups for Active Directory user groups.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • December 18, 2017 at 12:15 pm

    #1972568

    I get it. Thank you for your help guys!

Viewing 6 posts - 1 through 6 (of 6 total)

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