SQLLawman

Family man and SQL Geek

Stored Procedure with Execute as

By MichaelDeputy, 1 year and 3 months ago

We have a database that holds information that is fairly personal, so much that the application team that supports it may only have read access to the database.  There came a need to occasionally lock all the administrators out of the system for updates.  These updates will occur every few months.  So I created a SQL user and gave that user select and update rights to the necessary table.  Then I created a couple of stored procs, one to disable the logins and one to enable the logins.

CREATE PROCEDURE [dbo].[LockOutAdministrators]
WITH EXECUTE AS 'EXECUTIONER'
AS
BEGIN
BLAH, BLAH, BLAH
END

I then granted execute to the stored procedures to the application group.  So the application team can only update the table via the stored procedure that does only what it needs to do.  They are happy they have an easy way to do this without me becoming the bottle neck.  If this was a more sensitive system, I'd probably add some auditing, but it is not necessary at this time.

No comments

Be the first to write a comment on this post.

Write a comment

If you want to add your comment on this post, simply fill out the next form:





Anti-Spam Protection by WP-SpamFree

* Required fields

You can use these XHTML tags: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>.

No trackbacks

To notify a mention on this post in your blog, enable automated notification (Options > Discussion in WordPress) or specify this trackback url: http://​www.deputyfamily.net/​MichaelDeputy/​archive/​2009/​06/​stored-procedure-with-execute-as/​trackback/