With Execute As Not Working As Expected

Question

With Execute As Not Working As Expected

Dave Thomas

SSC Veteran

Points: 270
More actions
March 26, 2018 at 8:35 am

#344513

Can anyone tell me why this is not working (I obfuscated the password, BTW). I am sysadmin on the server ('sentara\dxthomas'). If I remove the "Execute As" and execute the SP everything works fine. If I include it, the error I get is:
Msg 15247, Level 16, State 1, Procedure sp_MSaddserver_internal, Line 34
User does not have permission to perform this action.
Msg 15247, Level 16, State 1, Procedure sp_addlinkedsrvlogin, Line 25
User does not have permission to perform this action.

Alter Procedure [dbo].[RemoteLink]
@LinkedServerName Varchar(50)
--With Encryption
With Execute As 'sentara\dxthomas'
As
Set NoCount On
Declare @ValidServer bit = 0
Declare @CurrentUser Varchar(50) = system_user
If @LinkedServerName='CLARITYANCETTA'
Set @ValidServer = 1
If Not Exists (Select 1 From sysservers Where srvname = 'CLARITYANCETTA' )
Begin
EXEC master.dbo.sp_addlinkedserver @server = N'CLARITYANCETTA', @srvproduct=N'SQLSERVER', @provider=N'SQLNCLI', @datasrc=N'CLARITYANCETTA', @catalog=N'HETS';
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'CLARITYANCETTA',@useself=N'False',@locallogin=NULL,@rmtuser=N'HetsUser',@rmtpassword='xxxx';
End

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Chris Harshman SSC-Forever Points: 42192 More actions · Answer 1

What database is this stored proc in? If this is not in MASTER, then you need to sign the stored proc with a certificate to be able to use EXECUTE AS across multiple databases.
https://docs.microsoft.com/en-us/sql/relational-databases/tutorial-signing-stored-procedures-with-a-certificate

Dave Thomas SSC Veteran Points: 270 More actions · Answer 2

Chris Harshman - Monday, March 26, 2018 10:54 AM
What database is this stored proc in? If this is not in MASTER, then you need to sign the stored proc with a certificate to be able to use EXECUTE AS across multiple databases.
https://docs.microsoft.com/en-us/sql/relational-databases/tutorial-signing-stored-procedures-with-a-certificate

I just created the SP in Master, switched my context to Master, and got the same error. If I remove the Execute As, the proc executes as expected.

Sue_H SSC Guru Points: 90860 More actions · Answer 3

Try specifying a login - something like:

Alter Procedure [dbo].[RemoteLink]

@LinkedServerName Varchar(50)

AS

--With Encryption

Execute As login = 'Domain\Account'

Sue

Jeff Moden SSC Guru Points: 1004407 More actions · Answer 4

Dave Thomas - Monday, March 26, 2018 8:35 AM
Can anyone tell me why this is not working (I obfuscated the password, BTW). I am sysadmin on the server ('sentara\dxthomas'). If I remove the "Execute As" and execute the SP everything works fine. If I include it, the error I get is:
Msg 15247, Level 16, State 1, Procedure sp_MSaddserver_internal, Line 34
User does not have permission to perform this action.
Msg 15247, Level 16, State 1, Procedure sp_addlinkedsrvlogin, Line 25
User does not have permission to perform this action.

Alter Procedure [dbo].[RemoteLink]
@LinkedServerName Varchar(50)
--With Encryption
With Execute As 'sentara\dxthomas'
As
Set NoCount On
Declare @ValidServer bit = 0
Declare @CurrentUser Varchar(50) = system_user
If @LinkedServerName='CLARITYANCETTA'
Set @ValidServer = 1
If Not Exists (Select 1 From sysservers Where srvname = 'CLARITYANCETTA' )
Begin
EXEC master.dbo.sp_addlinkedserver @server = N'CLARITYANCETTA', @srvproduct=N'SQLSERVER', @provider=N'SQLNCLI', @datasrc=N'CLARITYANCETTA', @catalog=N'HETS';
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'CLARITYANCETTA',@useself=N'False',@locallogin=NULL,@rmtuser=N'HetsUser',@rmtpassword='xxxx';
End

The correct syntax is

EXECUTE AS LOGIN = 'loginnamehere'

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004407 More actions · Answer 5

Sue_H - Monday, March 26, 2018 12:03 PM
Try specifying a login - something like:
Alter Procedure [dbo].[RemoteLink]
@LinkedServerName Varchar(50)
AS
--With Encryption
Execute As login = 'Domain\Account'
Sue

Sorry, Sue. Didn't see that you also posted on the correct syntax.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Sergiy SSC Guru Points: 110208 More actions · Answer 6

When you as a sysadmin create / alter a procedure on a user database it's saved as created by user 'dbo' of that database.

When you execute that procedure the code is executed in the context of used 'dbo' of that database, not the login you used to create it.

To allow that procedure to do things on the server you need to make the database it's saved in "trusted".

Them 'dbo' user of that database will have sysadmin privileges on the server.

P.S. Master database is not necessarily "trusted".

_____________
Code for TallyGenerator

Dave Thomas SSC Veteran Points: 270 More actions · Answer 7

Sue_H - Monday, March 26, 2018 12:03 PM
Try specifying a login - something like:
Alter Procedure [dbo].[RemoteLink]
@LinkedServerName Varchar(50)
AS
--With Encryption
Execute As login = 'Domain\Account'
Sue

This works, but the only people that can impersonate the login with the proper privileges are already sysadmin.

Dave Thomas SSC Veteran Points: 270 More actions · Answer 8

Sergiy - Tuesday, March 27, 2018 1:28 AM
When you as a sysadmin create / alter a procedure on a user database it's saved as created by user 'dbo' of that database.When you execute that procedure the code is executed in the context of used 'dbo' of that database, not the login you used to create it.To allow that procedure to do things on the server you need to make the database it's saved in "trusted".Them 'dbo' user of that database will have sysadmin privileges on the server.P.S. Master database is not necessarily "trusted".

When you say "trusted" do you mean "trustworthy"?

Dave Thomas SSC Veteran Points: 270 More actions · Answer 9

Solution!

When using

With Execute As
In a stored procedure, the database that the stored procedure is created in must be set to "Trustworthy = On"

Thanks to everyone for their help!

BTW, this whole effort is to allow users to create temporary linked servers that magically "dissappear" after they follow the "RemoteLink" sp with a query. Had to do it this way because LAN group has a bad time with SPN delegation and kerberose configuration.

Chris Harshman SSC-Forever Points: 42192 More actions · Answer 10

Somehow I don't think that setting the database to trustworthy is really the long term solution you're looking for. There are lots of articles that talk about the security problems with this:
https://www.google.com/search?q=sql+server+trustworthy+security+risk&oq=sql+server+trustworthy+&aqs=chrome.2.69i57j0l5.8006j0j8&sourceid=chrome&ie=UTF-8