The server principal "XYuser" is not able to access the database "Ydb" under the current security context
You cannot use ownership chaining when your stored procedure contains dynamic SQL, i.e doing so breaks the ownership chain.
In order for this to work you will need to use a certificate to sign your stored procedures.
Below is a brilliant article that contains instructions for signing stored procedures.
http://www.sommarskog.se/grantperm.html
Looking at this in further detail, the fact that you are using the “execute as clause” should negate the fact that the ownership chain is broken as a result of incorporating dynamic SQL.
With this in mind, the likely hood is that for some reason, the login “ReadUser” does not have appropriate read access to the databases in question, this should not be the case however, given that the login is a member of the db_owner role in both databases. That said, if the database roles have been altered from their original state then this may not hold true.
To test that the issue is not isolated to the “ReadUser” login I would suggest creating a new SQL Server Login, and mapping the login to both databases (there by creating database logins of the same name) with appropriate read access. Then modify the stored procedure to execute as the new login.
Lord Future
There is only one corner of the universe you can be certain of improving, and that's yourself.
Updated on June 04, 2022Comments
-
Lord Future almost 2 years
System Specifications
Microsoft SQL Server Management Studio 9.00.4035.00 Microsoft Analysis Services Client Tools 2005.090.4035.00 Microsoft Data Access Components (MDAC) 2000.085.1132.00 (xpsp.080413-0852) Microsoft MSXML 2.6 3.0 4.0 5.0 6.0 Microsoft Internet Explorer 7.0.5730.13 Microsoft .NET Framework 2.0.50727.1433 Operating System 5.1.2600
On an SQL Server 2005 called BHAVMSQL02, I have two databases Mattercentre_dev and CMSNET_DEV. The Mattercentre_dev has a stored procedure that builds a list from a table in CMSNET_DEV. The stored procedure looks like this...
USE [Mattercentre_dev] GO /****** Object: StoredProcedure [dbo].[UDSPRBHPRIMBUSTYPE] Script Date:02/12/2009 10:18:10 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER OFF GO ALTER PROCEDURE [dbo].[UDSPRBHPRIMBUSTYPE] WITH EXECUTE AS 'Readuser' AS DECLARE @SERVERNAME nvarchar(30) DECLARE @DBASE nvarchar(30) DECLARE @SQL nvarchar(2000) SET @SERVERNAME = Convert(nvarchar, (SELECT spData FROM dbSpecificData WHERE spLookup = 'CMSSERVER')) SET @DBASE = Convert(nvarchar, (SELECT spData FROM dbSpecificData WHERE spLookup = 'CMSDBNAME')) SET @SQL = 'SELECT null as Code , ''(not specified)'' as Description UNION SELECT clnt_cat_code as Code , clnt_cat_desc as Description FROM ' + @SERVERNAME + '.' + @DBASE + '.dbo.hbl_clnt_cat WHERE inactive = ''N'' ORDER BY Description' PRINT @SQL EXECUTE sp_executeSQL @SQL @SERVERNAME == 'BHAVMSQL02' @DBASE == 'CMSNET_DEV'
When the stored procedure was executed the following error message appeared...
The server principal "ReadUser" is not able to access the database "CMSNET_DEV" under the current security context.
After googling the error message, I carried out the following fix...
- Deleted the user ReadUser from BHAVMSQL02 -> Databases -> Mattercentre_dev -> Security -> Users
Set Up ReadUser from BHAVMSQL02 -> Security -> Logins with the following settings...
General
Login Name - readUser
Password - xxxxxxxxxxxx
Confirm - xxxxxxxxxxxx
Default db - master
default lg - British English
Everything Else - UnsetServer Roles Only Public Set
User Mappings CMSNET_DEV - ReadUser - dbo
Database Role Membership - db_owner, publicMattercentre_dev - ReadUser - dbo
Database Role Membership - db_owner, public
I then ran the following script...
ALTER DATABASE CMSNET_DEV SET TRUSTWORTHY ON GO ALTER DATABASE mattercentre_dev SET TRUSTWORTHY ON GO
I re-ran the stored procedure and executed it again and I still have the same error message.
I have looked this question up in Stack Overflow and the suggested solutions are similar to my own.