June 30, 2008 at 9:42 am
I have both a view and stored procedure that are exibiting the same problem. Each of them is accessing a table in a 2nd database. I have granted connect access to the database and select access to the table. However, they still fail. After doing some testing, I've boiled it down.
For db2, the user 'ward' has CONNECT granted. For the table db2.dbo.practice_name, the user has SELECT granted.
This works just fine. With db2,
execute as user = 'ward'
select * from practice_name
This fails with db1,
execute as user = 'ward'
select * from db2.dbo.practice_name
With db1, this fails as well:
execute as user = 'ward'
use db2
I get the error: The server principal 'ward' is not able to access the database 'db2' under the current security context.
It seems that within the database, the user can access the tables but cannot access the tables across databases.
Am I missing some security setting?
Rob
June 30, 2008 at 12:00 pm
When using EXECUTE AS to impersonate a user, the scope of impersonation is restricted to the current database. You can't use it to access objects in another database unless you use EXECUTE AS LOGIN. See "Extending Database Impersonation by using EXECUTE AS" in BOL.
Greg
July 1, 2008 at 5:51 am
Greg Charles (6/30/2008)
When using EXECUTE AS to impersonate a user, the scope of impersonation is restricted to the current database. You can't use it to access objects in another database unless you use EXECUTE AS LOGIN. See "Extending Database Impersonation by using EXECUTE AS" in BOL.
Ah! The fine print!
Thank you. I used the LOGIN option and was able to test the permissions. I found another table that needed permissions and I was able to resolve my problem.
Rob
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply