January 31, 2017 at 8:11 am
I've been playing around with permissions on a stored procedure and noticed that when using REVERT it wasn't changing the context back
Made Up example names:
WindowsUser1 is a WINDOWS_USER that is a service account that's been setup
WindowsGroup1 is an AD Group that's been added to manage permissions
I'm in the WindowsGroup1 group, which has permissions
GRANT IMPERSONATE ON User::[WindowsUser1] TO [WindowsGroup1];
GO
CREATE PROCEDURE [dbo].[TestSP]
WITH EXECUTE AS 'WindowsUser1'
AS
BEGIN
SET NOCOUNT ON;
SELECT USER_NAME(),ORIGINAL_LOGIN();
REVERT;
SELECT USER_NAME(),ORIGINAL_LOGIN();
END
GO
EXECUTE dbo.TestSP;
Result brings back user_name = WindowsUser1 in both SELECTs. What am I missing?
January 31, 2017 at 8:32 am
The context of the whole SP will be run as WindowsUsers1 when declaring WITH EXECUTE As. Thus there are no impersonations to REVERT.
Try this:CREATE PROCEDURE [dbo].[TestSP2]
AS
BEGIN
EXECUTE AS USER = 'WindowsUsers1';
SELECT USER_NAME(),ORIGINAL_LOGIN();
REVERT;
SELECT USER_NAME(),ORIGINAL_LOGIN();
END
GO
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
January 31, 2017 at 8:36 am
Thom A - Tuesday, January 31, 2017 8:32 AMThe context of the whole SP will be run as WindowsUsers1 when declaring WITH EXECUTE As. Thus there are no impersonations to REVERT.Try this:
CREATE PROCEDURE [dbo].[TestSP2]
AS
BEGIN
EXECUTE AS USER = 'WindowsUsers1';SELECT USER_NAME(),ORIGINAL_LOGIN();
REVERT;
SELECT USER_NAME(),ORIGINAL_LOGIN();
END
GO
Ta - knew it would be something simple I wasn't seeing!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply