Can you run a SQL Server stored procedure using a login held in that procedure
0
I have a stored procedure that requires the use of the xp_dirtree function.
This function currently only works when run by our SA account as it needs extra permissions to read a servers file system.
I am looking to get this to run off a VB.Net program but for obvious reasons I do not want to put the SA login details on the SQL Server connection string.
We have other use accounts that we use for accessing data from our programs.
My question: is there a way within the stored procedure code within SQL Server to execute this code something like:
Run AS: Login:'SA';Password:'xxxxxxx'
so that non-elevated accounts can execute the procedure, but it is then run under the elevated account?
Kind regards
Matt
sql-server-2008 stored-procedures permissions
edited Nov 22 '18 at 19:20
marc_s
580k13011191266
asked Nov 22 '18 at 16:50
Matt BartlettMatt Bartlett
10812
add a comment |
0
I have a stored procedure that requires the use of the xp_dirtree function.
This function currently only works when run by our SA account as it needs extra permissions to read a servers file system.
I am looking to get this to run off a VB.Net program but for obvious reasons I do not want to put the SA login details on the SQL Server connection string.
We have other use accounts that we use for accessing data from our programs.
My question: is there a way within the stored procedure code within SQL Server to execute this code something like:
Run AS: Login:'SA';Password:'xxxxxxx'
so that non-elevated accounts can execute the procedure, but it is then run under the elevated account?
Kind regards
Matt
sql-server-2008 stored-procedures permissions
edited Nov 22 '18 at 19:20
marc_s
580k13011191266
asked Nov 22 '18 at 16:50
Matt BartlettMatt Bartlett
10812
add a comment |
0
0
0
I have a stored procedure that requires the use of the xp_dirtree function.
This function currently only works when run by our SA account as it needs extra permissions to read a servers file system.
I am looking to get this to run off a VB.Net program but for obvious reasons I do not want to put the SA login details on the SQL Server connection string.
We have other use accounts that we use for accessing data from our programs.
My question: is there a way within the stored procedure code within SQL Server to execute this code something like:
Run AS: Login:'SA';Password:'xxxxxxx'
so that non-elevated accounts can execute the procedure, but it is then run under the elevated account?
Kind regards
Matt
sql-server-2008 stored-procedures permissions
edited Nov 22 '18 at 19:20
marc_s
580k13011191266
asked Nov 22 '18 at 16:50
Matt BartlettMatt Bartlett
10812
I have a stored procedure that requires the use of the xp_dirtree function.
This function currently only works when run by our SA account as it needs extra permissions to read a servers file system.
I am looking to get this to run off a VB.Net program but for obvious reasons I do not want to put the SA login details on the SQL Server connection string.
We have other use accounts that we use for accessing data from our programs.
My question: is there a way within the stored procedure code within SQL Server to execute this code something like:
Run AS: Login:'SA';Password:'xxxxxxx'
so that non-elevated accounts can execute the procedure, but it is then run under the elevated account?
Kind regards
Matt
sql-server-2008 stored-procedures permissions
sql-server-2008 stored-procedures permissions
edited Nov 22 '18 at 19:20
marc_s
580k13011191266
asked Nov 22 '18 at 16:50
Matt BartlettMatt Bartlett
10812
edited Nov 22 '18 at 19:20
marc_s
580k13011191266
asked Nov 22 '18 at 16:50
Matt BartlettMatt Bartlett
10812
edited Nov 22 '18 at 19:20
marc_s
580k13011191266
edited Nov 22 '18 at 19:20
marc_s
580k13011191266
edited Nov 22 '18 at 19:20
marc_s
580k13011191266
580k13011191266
asked Nov 22 '18 at 16:50
Matt BartlettMatt Bartlett
10812
asked Nov 22 '18 at 16:50
Matt BartlettMatt Bartlett
10812
asked Nov 22 '18 at 16:50
Matt BartlettMatt Bartlett
10812
10812
add a comment |
add a comment |
1 Answer
1
active
oldest
votes
1
Use Execute As
CREATE PROCEDURE dbo.usp_Demo
WITH EXECUTE AS 'SA'
AS
SELECT user_name(); -- Shows execution context is set to SA.
EXECUTE AS CALLER;
SELECT user_name(); -- Shows execution context is set to the caller of the module.
REVERT;
SELECT user_name(); -- Shows execution context is set to SA.
GO
answered Nov 22 '18 at 17:59
bastos.sergiobastos.sergio
5,73132031
Hi Bastos. With this code sample it doesn't show anywhere a request for an SA account password. Does this mean that anyone that knows the SA username can then run scripts using that accounts permissions?
– Matt Bartlett
Nov 23 '18 at 9:16
It means anyone invoking that SP, will invoke it under the permissions of the SA user. The calling user won’t even know that the SP is internally impersonating another user.
– bastos.sergio
Nov 23 '18 at 9:45
Thanks for the code. I'm going to have to look in to our permissions further as I tried running the code above as a test and now I get the message "Cannot execute as the user 'xxxxx', because it does not exist or you do not have permission." This is even though I am running the code logged in to the SA account, which does exists and should have all the permission needed.
– Matt Bartlett
Nov 23 '18 at 10:41
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53435376%2fcan-you-run-a-sql-server-stored-procedure-using-a-login-held-in-that-procedure%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
1
Use Execute As
CREATE PROCEDURE dbo.usp_Demo
WITH EXECUTE AS 'SA'
AS
SELECT user_name(); -- Shows execution context is set to SA.
EXECUTE AS CALLER;
SELECT user_name(); -- Shows execution context is set to the caller of the module.
REVERT;
SELECT user_name(); -- Shows execution context is set to SA.
GO
answered Nov 22 '18 at 17:59
bastos.sergiobastos.sergio
5,73132031
Hi Bastos. With this code sample it doesn't show anywhere a request for an SA account password. Does this mean that anyone that knows the SA username can then run scripts using that accounts permissions?
– Matt Bartlett
Nov 23 '18 at 9:16
It means anyone invoking that SP, will invoke it under the permissions of the SA user. The calling user won’t even know that the SP is internally impersonating another user.
– bastos.sergio
Nov 23 '18 at 9:45
Thanks for the code. I'm going to have to look in to our permissions further as I tried running the code above as a test and now I get the message "Cannot execute as the user 'xxxxx', because it does not exist or you do not have permission." This is even though I am running the code logged in to the SA account, which does exists and should have all the permission needed.
– Matt Bartlett
Nov 23 '18 at 10:41
add a comment |
1
Use Execute As
CREATE PROCEDURE dbo.usp_Demo
WITH EXECUTE AS 'SA'
AS
SELECT user_name(); -- Shows execution context is set to SA.
EXECUTE AS CALLER;
SELECT user_name(); -- Shows execution context is set to the caller of the module.
REVERT;
SELECT user_name(); -- Shows execution context is set to SA.
GO
answered Nov 22 '18 at 17:59
bastos.sergiobastos.sergio
5,73132031
Hi Bastos. With this code sample it doesn't show anywhere a request for an SA account password. Does this mean that anyone that knows the SA username can then run scripts using that accounts permissions?
– Matt Bartlett
Nov 23 '18 at 9:16
It means anyone invoking that SP, will invoke it under the permissions of the SA user. The calling user won’t even know that the SP is internally impersonating another user.
– bastos.sergio
Nov 23 '18 at 9:45
Thanks for the code. I'm going to have to look in to our permissions further as I tried running the code above as a test and now I get the message "Cannot execute as the user 'xxxxx', because it does not exist or you do not have permission." This is even though I am running the code logged in to the SA account, which does exists and should have all the permission needed.
– Matt Bartlett
Nov 23 '18 at 10:41
add a comment |
1
1
1
Use Execute As
CREATE PROCEDURE dbo.usp_Demo
WITH EXECUTE AS 'SA'
AS
SELECT user_name(); -- Shows execution context is set to SA.
EXECUTE AS CALLER;
SELECT user_name(); -- Shows execution context is set to the caller of the module.
REVERT;
SELECT user_name(); -- Shows execution context is set to SA.
GO
answered Nov 22 '18 at 17:59
bastos.sergiobastos.sergio
5,73132031
Use Execute As
CREATE PROCEDURE dbo.usp_Demo
WITH EXECUTE AS 'SA'
AS
SELECT user_name(); -- Shows execution context is set to SA.
EXECUTE AS CALLER;
SELECT user_name(); -- Shows execution context is set to the caller of the module.
REVERT;
SELECT user_name(); -- Shows execution context is set to SA.
GO
answered Nov 22 '18 at 17:59
bastos.sergiobastos.sergio
5,73132031
answered Nov 22 '18 at 17:59
bastos.sergiobastos.sergio
5,73132031
answered Nov 22 '18 at 17:59
bastos.sergiobastos.sergio
5,73132031
answered Nov 22 '18 at 17:59
bastos.sergiobastos.sergio
5,73132031
5,73132031
Hi Bastos. With this code sample it doesn't show anywhere a request for an SA account password. Does this mean that anyone that knows the SA username can then run scripts using that accounts permissions?
– Matt Bartlett
Nov 23 '18 at 9:16
It means anyone invoking that SP, will invoke it under the permissions of the SA user. The calling user won’t even know that the SP is internally impersonating another user.
– bastos.sergio
Nov 23 '18 at 9:45
Thanks for the code. I'm going to have to look in to our permissions further as I tried running the code above as a test and now I get the message "Cannot execute as the user 'xxxxx', because it does not exist or you do not have permission." This is even though I am running the code logged in to the SA account, which does exists and should have all the permission needed.
– Matt Bartlett
Nov 23 '18 at 10:41
add a comment |
Hi Bastos. With this code sample it doesn't show anywhere a request for an SA account password. Does this mean that anyone that knows the SA username can then run scripts using that accounts permissions?
– Matt Bartlett
Nov 23 '18 at 9:16
It means anyone invoking that SP, will invoke it under the permissions of the SA user. The calling user won’t even know that the SP is internally impersonating another user.
– bastos.sergio
Nov 23 '18 at 9:45
Thanks for the code. I'm going to have to look in to our permissions further as I tried running the code above as a test and now I get the message "Cannot execute as the user 'xxxxx', because it does not exist or you do not have permission." This is even though I am running the code logged in to the SA account, which does exists and should have all the permission needed.
– Matt Bartlett
Nov 23 '18 at 10:41
Hi Bastos. With this code sample it doesn't show anywhere a request for an SA account password. Does this mean that anyone that knows the SA username can then run scripts using that accounts permissions?
– Matt Bartlett
Nov 23 '18 at 9:16
Hi Bastos. With this code sample it doesn't show anywhere a request for an SA account password. Does this mean that anyone that knows the SA username can then run scripts using that accounts permissions?
– Matt Bartlett
Nov 23 '18 at 9:16
It means anyone invoking that SP, will invoke it under the permissions of the SA user. The calling user won’t even know that the SP is internally impersonating another user.
– bastos.sergio
Nov 23 '18 at 9:45
It means anyone invoking that SP, will invoke it under the permissions of the SA user. The calling user won’t even know that the SP is internally impersonating another user.
– bastos.sergio
Nov 23 '18 at 9:45
Thanks for the code. I'm going to have to look in to our permissions further as I tried running the code above as a test and now I get the message "Cannot execute as the user 'xxxxx', because it does not exist or you do not have permission." This is even though I am running the code logged in to the SA account, which does exists and should have all the permission needed.
– Matt Bartlett
Nov 23 '18 at 10:41
Thanks for the code. I'm going to have to look in to our permissions further as I tried running the code above as a test and now I get the message "Cannot execute as the user 'xxxxx', because it does not exist or you do not have permission." This is even though I am running the code logged in to the SA account, which does exists and should have all the permission needed.
– Matt Bartlett
Nov 23 '18 at 10:41
add a comment |
draft saved
draft discarded
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53435376%2fcan-you-run-a-sql-server-stored-procedure-using-a-login-held-in-that-procedure%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
