title: The server principal "XYuser" is not able to access the database "Ydb" under the current security context
body: <h1>System Specifications</h1>
  
  <pre><code>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  
  </code></pre>
  
  <p>On an SQL Server 2005 called <strong>BHAVMSQL02</strong>, I have two databases <em>Mattercentre_dev</em> and <em>CMSNET_DEV</em>. The <em>Mattercentre_dev</em> has a stored procedure that builds a list from a table in <em>CMSNET_DEV</em>. The stored procedure looks 
  like this...</p>
  
  <pre><code>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'
  </code></pre>
  
  <p>When the stored procedure was executed the following error message appeared...</p>
  
  <pre><code>The server principal "ReadUser" is not able to access the database "CMSNET_DEV" under the current security context.
  </code></pre>
  
  <p>After googling the error message, I carried out the following fix...</p>
  
  <ul>
  <li>Deleted the user ReadUser from
  BHAVMSQL02 -> Databases ->
  Mattercentre_dev -> Security -> Users</li>
  <li><p>Set Up ReadUser from BHAVMSQL02 ->
  Security -> Logins with the following
  settings...</p>
  
  <p>General<br>
  Login Name - readUser<br>
  Password - xxxxxxxxxxxx<br>
  Confirm - xxxxxxxxxxxx<br>
  Default db - master<br>
  default lg - British English<br>
  Everything Else - Unset  </p>
  
  <p>Server Roles
  Only Public Set</p>
  
  <p>User Mappings
  CMSNET_DEV - ReadUser - dbo<br>
  Database Role Membership - db_owner, public  </p>
  
  <p>Mattercentre_dev - ReadUser - dbo<br>
  Database Role Membership - db_owner, public </p></li>
  </ul>
  
  <p>I then ran the following script...</p>
  
  <pre><code>ALTER DATABASE CMSNET_DEV SET TRUSTWORTHY ON
  GO
  ALTER DATABASE mattercentre_dev SET TRUSTWORTHY ON
  GO
  </code></pre>
  
  <p>I re-ran the stored procedure and executed it again and I still have the same
  error message.</p>
  
  <p>I have looked this question up in Stack Overflow and the suggested solutions
  are similar to my own.</p>