Obtaining the Following Error in SQL Server 2005 <The server principal "XYuser" is not able to access the database "Ydb" under the current security context.>

Question

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 - Unset

Server Roles Only Public Set

User Mappings CMSNET_DEV - ReadUser - dbo
Database Role Membership - db_owner, public

Mattercentre_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.

Can anyone suggest a solution?

Answer 1

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

Updated: 13/02/09

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.

Cheers, John