July 30, 2009 at 8:53 am
This is a good one, I have a stored proc in database A that first deletes some rows from a table in database A. it then does a delete from a synoym that references a table in a different database. We were trying to use a "execute as" with a windows sql account "domain\srvc" that has sysadmin rights. The sp is executed with a sql account with limited security. First can you use a windows\sql account like this, and secondly can you do it with a synonym?
July 30, 2009 at 10:08 am
yes you can as long as that user has appropriate rights for those tables that the sp is modifying.
July 30, 2009 at 10:33 am
OK now, synonym is not the issue. I have a windows\sql account that has sysamdin rights.
I have a procedure in database A that the executing user has rights to. This procedure deletes records from a table in database B.
The stored proc looks like this
I have a table in database B which the procedure in database A
alter procedure test
WITH EXECUTE AS 'stlntdom\jsmith'
AS
BEGIN
select * from plandata_uat.dbo.test1
END
Jsmith has sysadmin rights and is a user in the from plandata_uat.dbo.test1
When I execute this stored proc as a user who has right to execute this stored proc I get
The server principal "'stlntdom\jsmith" is not able to access the database "plandata_UAT" under the current security context.
July 30, 2009 at 11:37 am
That's because you are executing as stlntdom\jsmith and you have not given 'stlntdom\jsmith' access to go through your linked server.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
July 30, 2009 at 1:07 pm
Not on different server all on same bxo
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply