locked
Loginless User access other database RRS feed

  • Question

  • Hi

       We have two database "Database-A" and "Database-B" on same server. In "Database-B" we have user without login called "LoginLessUser-B". Now in "Database-B" we have view called "MyDataView" which get the data from "Database-A" thought sql called "Select * from Database-A.MyData". Here is complete definition of view in "Database-B"

    Create View MyDataView as

    Select * from Database-A.MyData

    go

    now in "Database-B" I changed security context to "LoginLessUser-B" by using command "Execute as User='LoginLessUser-B'"

    and when I issue "delete from MyDataView where id=5" it gives me following error

    The server principal "S-1-9-3-940756221-1214167105-3020107912-2726908893." is not able to access the database "Database-A" under the current security context.

    is there anyway is possible to allows "LoginLessUser-B" to perform this operation ?

    thanks in advance


    Drew

    Thursday, November 7, 2013 2:37 PM

Answers

  • Also discussed in Books Online topic Security Best Practices with Contained Databases http://technet.microsoft.com/en-us/library/ff929055.aspx section called Creating a Duplicate User in Another Database.

    Rick Byham, Microsoft, SQL Server Books Online, Implies no warranty

    • Proposed as answer by Sofiya Li Friday, November 8, 2013 8:30 AM
    • Marked as answer by drew_p Friday, November 8, 2013 2:08 PM
    Thursday, November 7, 2013 4:26 PM

All replies

  • is there anyway is possible to allows "LoginLessUser-B" to perform this operation ?

    By default cross-database ownership is disable and I hardley suggest to keep it disable.

    Instead you may use a certificate user + impersonate to access objects in the other database.


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Thursday, November 7, 2013 3:07 PM
  • we are migrating database from Sybase to sql server. So we want to keep it simple. May be can use Server Login which has access to both database ?

    Drew

    Thursday, November 7, 2013 3:44 PM
  • Also discussed in Books Online topic Security Best Practices with Contained Databases http://technet.microsoft.com/en-us/library/ff929055.aspx section called Creating a Duplicate User in Another Database.

    Rick Byham, Microsoft, SQL Server Books Online, Implies no warranty

    • Proposed as answer by Sofiya Li Friday, November 8, 2013 8:30 AM
    • Marked as answer by drew_p Friday, November 8, 2013 2:08 PM
    Thursday, November 7, 2013 4:26 PM
  • Yes, that is a better option. Note that when you test, you need to use EXECUTE AS LOGIN. With EXECUTE AS USER, you are sandboxed into the current database.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Proposed as answer by Sofiya Li Friday, November 8, 2013 8:30 AM
    Thursday, November 7, 2013 10:47 PM