none
The server principal "XYuser" is not able to access the database "Ydb" under the current security context

    Question

  • SQL2005 on winserver 2003. I have a view in Xdb that accesses tables in 2 different databases (Xdb and Ydb) on the same server. I have mixed mode security. I have a SQL user (XYuser) that has read access to all tables and views on both databases, yet when I try to access the view using a C# windows application I get the following error:

     

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

     

    This same scenario works under SQL 2000. I looked through the postings and tried to set TRUSTWORTHY ON on both databases but that didn't help. I can access any other views or tables on the SQL 2005 server, just not the one that joins the tables cross databases. Any help is much appreciated... john

    Wednesday, May 23, 2007 3:14 PM

Answers

All replies

  • Do you have cross database ownership chaining enabled in SQL 2000?  If this is the reason the view works then you can enable it in SQL 2005 but I wouldn't enable it unless your application truly requires it.

     

    HTH,

     

    -Steven Gott

    S/DET

    SQL Server

     

     

     

     

    Thursday, May 24, 2007 3:35 PM
    Moderator
  • I'm not sure. Is there an easy way to see if this is enabled. I can see how to enable it either on a whole instance of SQL Server with sp_configure or on individual databases with the ALTER DATABASE statement. I'm not sure how to check to see what options are set on/off though. This may be what we need. Is this the only way to access a view that accesses tables from multiple databases?

     

    Thanks, john

    Thursday, May 24, 2007 4:06 PM
  • This should tell you if the dbs have cross database ownership chaining enabled.

     

    exec sp_dboption 'Xdb' , 'db chaining'

    exec sp_dboption 'Ydb' , 'db chaining'

     

    -Steven Gott

    S/DET

    SQL Server

    Thursday, May 24, 2007 4:52 PM
    Moderator
  • It doesn't look like that's an option in SQL 2000. When I run exec sp_dboption on the SQL 2000 server I get the following list:

     

    ANSI null default
    ANSI nulls
    ANSI padding
    ANSI warnings
    arithabort
    auto create statistics
    auto update statistics
    autoclose
    autoshrink
    concat null yields null
    cursor close on commit
    dbo use only
    default to local cursor
    merge publish
    numeric roundabort
    offline
    published
    quoted identifier
    read only
    recursive triggers
    select into/bulkcopy
    single user
    subscribed
    torn page detection
    trunc. log on chkpt.

     

    On my 2005 server I get this list which includes db chaining:

     

    ANSI null default
    ANSI nulls
    ANSI padding
    ANSI warnings
    arithabort
    auto create statistics
    auto update statistics
    autoclose
    autoshrink
    concat null yields null
    cursor close on commit
    db chaining
    dbo use only
    default to local cursor
    merge publish
    numeric roundabort
    offline
    published
    quoted identifier
    read only
    recursive triggers
    select into/bulkcopy
    single user
    subscribed
    torn page detection
    trunc. log on chkpt.

    Thursday, May 24, 2007 10:35 PM
  • What SP are you running?  In SQL 2000 SP3 we added cross database ownership chainging restrictions.

     

    see http://msdn2.microsoft.com/en-us/library/aa905173(SQL.80).aspx 

     

    HTH,

     

    -Steven Gott

    S/DET

    SQL Server

    Thursday, May 24, 2007 11:46 PM
    Moderator
  •  Hi,

     

    I am getting this same kind of error with my application. I have 5 logins all are getting the same error:

     The server principal "XYuser" is not able to access the database "Ydb" under the current security context.

     

    I was trying to delete the login and re-create and it was successful for one login. But the second one doesnt allow to re-create with the message "The server principal 'xyz' already exists.

     

    Please let me know what kind of error is this? I am new to sql server 2005.

    Wednesday, May 21, 2008 11:21 AM
  •  

    This appears to be a Login/Database Mapping issue.  I was having this problem, but was able to resolve it as follows:

     

    Using the SQL Server management Studio:

    In the Object explorer, under the SERVER security folder (not the database security folder), expand Logins. 

    That is: ServerName -> Security -> Logins

    NOT: ServerName -> Databases -> DatabaseName -> Security -> Users

    Select the Login that is having the troubles.  Right click on the Login and select ‘Properties.’

     

    The ‘User Mapping’ page should list all databases on the server with a check mark on the databases that the Login has been mapped to.  When I was getting the error, the database in question was not checked (even though the Login was assigned as a User on the database itself).  Map the Login by checking the box next to the database name.  Set the default schema.  Then select the roles for the Login in the Database role membership list box.  I selected db_datareader and public.  After clicking OK to save the changes, the problem was resolved.

     

    In order to ‘Map’ the Login, the Login must not already be as User on the database, so you may have to go to the database security (ServerName -> Databases -> DatabaseName -> Security -> Users) and delete the Login from the list of database Users before mapping the Login to the database.
    Tuesday, June 03, 2008 6:03 PM
  • What to do incase the rights are assigned to a windows group & one of the users from that group is getting this message.
    Same user is also not able to see SQL Agent while the others from the same group are able to see that.

    The server principal "DOMAIN\user" is not able to access the database "Database" under the current security context.


    yup
    Tuesday, June 23, 2009 4:03 AM
  • The solution is pretty simple:

    http://www.kf7.co.uk/sql-server-principal-not-able-access-database.aspx

    SQL Server Management Studio uses sql script to get a list of databases and related information. If you do not have enough rights to retrieve the related pieces of information then you may get this error.
    Saturday, July 04, 2009 10:05 PM
  • This worked for me ..Thanks
    Tuesday, July 27, 2010 9:14 AM
  • Hi,

    I had the same proble and solved like this:-

    Please try using the following workaround and let us know if that helps:
    1) Bring Object Explorer Details window by selecting View --> Object Explorer Details in menu (or hitting F7)
    2) In Object Explorer window click at Databases folder
    3) In Object Explorer Details window right-click at the column header and deselect Collation
    4) Refresh Databases folder.

    bye

    Rahul

     

    Tuesday, December 07, 2010 7:36 AM
  • Hi,

    I had the same problem when using SQL Server Queue and solved it by giving my login the db_datareader right for master db.

    God speed your plough,
    Fritzerich

    Thursday, December 09, 2010 11:40 AM
  • Hi Rahul

    Your Simple solution just work!

    Thank you very much!

    Bye

    Monday, January 10, 2011 10:40 AM
  •  

    I am posting this for future readers.

     

    I found this:

    http://blogs.technet.com/b/mdegre/archive/2010/08/29/the-server-principal-quot-sqlloginname-quot-is-not-able-to-access-the-database-quot-mydatabasename-quot-under-the-current-security-context.aspx

     

    I am posting the working code (from the article above) just in case the article ceases to exist in the future.

    Basically, the Sql Server Login (version of the userName) has become out of sync with the Database(user) version of the userName.

     


    exec sp_change_users_login  @Action='Report';

    declare @UserName varchar(64)
    select @UserName = 'someUserName'

    /*
    SELECT left(name,48) as name, sid FROM sys.sysusers dbUsers WHERE name = @UserName
    SELECT left(name,48) as name, sid FROM sys.syslogins sqlServerLogins WHERE name = @UserName
    */

    SELECT left(dbUsers.name,48) as name, dbUsers.sid as sid1 , sqlServerLogins.sid as sid2 FROM sys.sysusers dbUsers
    join sys.syslogins sqlServerLogins on UPPER(dbUsers.name) = UPPER(sqlServerLogins.name)
    WHERE dbUsers.name = @UserName and dbUsers.sid != sqlServerLogins.sid

    declare @FixItUserName varchar(64)
    select @FixItUserName = NULL

    select @FixItUserName = (SELECT dbUsers.name
    FROM sys.sysusers dbUsers
    join sys.syslogins sqlServerLogins on UPPER(dbUsers.name) = UPPER(sqlServerLogins.name)
    WHERE dbUsers.name = @UserName and dbUsers.sid != sqlServerLogins.sid )

    /* RBAR (Row by Agonizing Row) but keep running this script until all are fixed (aka, "No more orphans")*/
    if (@FixItUserName IS NOT NULL)
    begin
        print 'Fixing the login/dbuser name of:::'
        print @FixItUserName
        print ''
        EXEC  sp_change_users_login @Action='update_one', @UserNamePattern=@FixItUserName,@LoginName=@FixItUserName;
    end
    else
    begin
        print 'No more orphans'
    end


    • Edited by LoudRock Thursday, May 26, 2011 6:29 PM misspelling
    Thursday, May 26, 2011 6:24 PM
  • Thank you!!!

     

    I had the same problem, and your instructions solve it.

     

    Thanks again!

    Wednesday, July 20, 2011 3:01 PM