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.
.
.
.