Q:
execute as with synonym
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
ASKED:
Jul 30 2009 3:45 PM GMT
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
A:
RATE THIS ANSWER
0
Click to Vote:
0
Click to Vote:
- 0
0
1. Create the new Login for domain\srvc and then create a database user from the Login in database A.
USE database A
GO
CREATE USER [domain\srvc] FROM LOGIN [domain\srvc]
2. Grant authority to the User domain\srvc in database A to enable them to delete data from the table.
3. Create a database user from the Login domain\srvc in database B.
USE database B
GO
CREATE USER [domain\srvc] FROM LOGIN [domain\srvc]
4. Grant authority to the User domain\srvc in database B to enable them to delete data from the table.
5. Create the synonym in Database A, referencing the table in database B.
6. Create the stored procedure in database A specifying the with execute as 'domain\srvc' option.
7. Change the trustworthy setting for Database A to ON
ALTER DATABASE Database A SET TRUSTWORTHY ON
8. Grant execute permission to the Stored Procesure to to any database users who need to call it.
