Stored Procedure with Execute as
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.
Delicious
Digg
Furl
Magnolia
Reddit
Slashdot
Stumbleupon
Technorati Favorites
Twitter
Write a comment
No trackbacks
.
.
.
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:
* 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/