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

Answer 1

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

 

 

 

 

Answer 2

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

Answer 3

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

Answer 4

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.

Answer 5

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

Answer 6

 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.

Answer 7

 

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.

Answer 8

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

Answer 9

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.

Answer 10

This worked for me ..Thanks

Answer 11

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

 

Answer 12

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

Answer 13

Hi Rahul

Your Simple solution just work!

Thank you very much!

Bye

Answer 14

 

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

Answer 15

Thank you!!!

 

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

 

Thanks again!