execute as with synonym

95 pts.
Tags:
SQL Database
SQL stored procedures
Stored Procedures
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 "domainsrvc" that has sysadmin rights. The sp is executed with a sql account with limited security. First can you use a windowssql account like this, and secondly can you do it with a synonym

Answer Wiki

Thanks. We'll let you know when a new response is added.

Yes this can be done.

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.

Discuss This Question:  

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following