January 28, 2011 at 8:51 am
Hello!
I have three databases, let's call them A, B and C.
From B and C I need to access data in A.
Therefore stored procedures are created with the clause
WITH EXECUTE AS 'ProcedureExecutor'
ProcedureExector is a login and exists as a user in all three databases.
From database B to A this works like a charm. But from database C to A, the same setup doesn't work. When executing the stored procedure returns:
The server principal "ProcedureExecutor " is not able to access the database "A" under the current security context.
I haven't been able to identify any differences between B and C.
I have tested the following with no success:
- Removing the user ProcedureExecutor from A and C and then adding it.
- Running sp_change_Users_login with 'autofix' option in both A and C.
- The object details/ collation -trick: http://www.kf7.co.uk/sql-server-principal-not-able-access-database.aspx
- I have even made ProcedureExecutor sysadmin, started up a new SSMS session and executed the procedure: But the problem remains!
Server details:
Microsoft SQL Server 2008 (SP1) - 10.0.2531.0 (Intel X86) Mar 29 2009 10:27:29 Copyright (c) 1988-2008 Microsoft Corporation Standard Edition on Windows NT 5.2 <X86> (Build 3790: Service Pack 2)
It is a virtual machine Running on WMWare ESX
January 28, 2011 at 8:56 am
Is it possible there are two versions of the login, tied to different server-level user IDs?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 28, 2011 at 9:51 am
Hmm, how can I identify such an issue?
January 28, 2011 at 10:02 am
Check out sys.sysusers. The "sid" column matches up the PK in sys.syslogins.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply