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.