Can you run a SQL Server stored procedure using a login held in that procedure
up vote
0
down vote
favorite
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 at 19:20
marc_s
568k12810991249
asked Nov 22 at 16:50
Matt Bartlett
1039
add a comment |
up vote
0
down vote
favorite
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 at 19:20
marc_s
568k12810991249
asked Nov 22 at 16:50
Matt Bartlett
1039
add a comment |
up vote
0
down vote
favorite
up vote
0
down vote
favorite
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 at 19:20
marc_s
568k12810991249
asked Nov 22 at 16:50
Matt Bartlett
1039
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 at 19:20
marc_s
568k12810991249
asked Nov 22 at 16:50
Matt Bartlett
1039
edited Nov 22 at 19:20
marc_s
568k12810991249
asked Nov 22 at 16:50
Matt Bartlett
1039
edited Nov 22 at 19:20
marc_s
568k12810991249
edited Nov 22 at 19:20
marc_s
568k12810991249
edited Nov 22 at 19:20
marc_s
568k12810991249
568k12810991249
asked Nov 22 at 16:50
Matt Bartlett
1039
asked Nov 22 at 16:50
Matt Bartlett
1039
asked Nov 22 at 16:50
Matt Bartlett
1039
1039
add a comment |
add a comment |
1 Answer
1
active
oldest
votes
up vote
1
down vote
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 at 17:59
bastos.sergio
5,68831931
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 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 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 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',
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
});
}
});
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
up vote
1
down vote
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 at 17:59
bastos.sergio
5,68831931
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 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 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 at 10:41
add a comment |
up vote
1
down vote
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 at 17:59
bastos.sergio
5,68831931
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 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 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 at 10:41
add a comment |
up vote
1
down vote
up vote
1
down vote
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 at 17:59
bastos.sergio
5,68831931
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 at 17:59
bastos.sergio
5,68831931
answered Nov 22 at 17:59
bastos.sergio
5,68831931
answered Nov 22 at 17:59
bastos.sergio
5,68831931
answered Nov 22 at 17:59
bastos.sergio
5,68831931
5,68831931
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 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 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 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 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 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 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 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 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 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 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 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 at 10:41
add a comment |
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.
Some of your past answers have not been well-received, and you're in danger of being blocked from answering.
Please pay close attention to the following guidance:
- 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.
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
