Executing a stored procedure which selects and inserts into tables in SQL ServerSQL Server stored procedure permissionsHow to change database owner for login and database created inside stored procedure?Update code works in manual stored procedure but not when run as SQL Server Agent JobMost efficient way to insert rows into a temp table in a stored procedureSQL Server : create table within stored procedureSQL Server CREATE and DROP TABLE Permissions in Stored Procedure Onlystored procedure can select and update tables in other databases - minimal permissions grantedStored Procedure Processing and Error LogGive execute permission to user which does not have execute permission for specific stored procedureHow to create a stored procedure that can insert a record into two tables [Informix]
Rivers without rain
Is it possible to measure lightning discharges as Nikola Tesla?
Help to reproduce a tcolorbox with a decoration
Why do Computer Science majors learn Calculus?
Is there anything in our scriptures where it says something similar to "As a man thinketh in his heart, so is he"
Is there a way to get a compiler for the original B programming language?
How can I practically buy stocks?
Do I have to worry about players making “bad” choices on level up?
Does this extra sentence in the description of the warlock's Eyes of the Rune Keeper eldritch invocation appear in any official reference?
How can the Zone of Truth spell be defeated without the caster knowing?
How much cash can I safely carry into the USA and avoid civil forfeiture?
Don’t seats that recline flat defeat the purpose of having seatbelts?
Mac Pro install disk keeps ejecting itself
Alternatives to Overleaf
How to verbalise code in Mathematica?
Pulling the rope with one hand is as heavy as with two hands?
What does KSP mean?
Will tsunami waves travel forever if there was no land?
Why was the Spitfire's elliptical wing almost uncopied by other aircraft of World War 2?
What is the difference between `a[bc]d` (brackets) and `ab,cd` (braces)?
Can someone publish a story that happened to you?
Inner for loop when run in background in bash spawns new bash process
Please, smoke with good manners
How to stop co-workers from teasing me because I know Russian?
Executing a stored procedure which selects and inserts into tables in SQL Server
SQL Server stored procedure permissionsHow to change database owner for login and database created inside stored procedure?Update code works in manual stored procedure but not when run as SQL Server Agent JobMost efficient way to insert rows into a temp table in a stored procedureSQL Server : create table within stored procedureSQL Server CREATE and DROP TABLE Permissions in Stored Procedure Onlystored procedure can select and update tables in other databases - minimal permissions grantedStored Procedure Processing and Error LogGive execute permission to user which does not have execute permission for specific stored procedureHow to create a stored procedure that can insert a record into two tables [Informix]
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty margin-bottom:0;
I am a bit confused on the permissions required to execute stored procedure. For example, if you have 2 tables and a stored procedure which reads data from Table1 and inserts data into Table2, will execute permission on the stored procedure and select permission on the tables be enough? In theory I am finding that this is given as a solution but practically I am finding that a user would need execute permission on the stored procedure, select permission on the tables and insert permission on Table2.
sql-server stored-procedures
asked 2 hours ago
user1930901user1930901
494
add a comment |
I am a bit confused on the permissions required to execute stored procedure. For example, if you have 2 tables and a stored procedure which reads data from Table1 and inserts data into Table2, will execute permission on the stored procedure and select permission on the tables be enough? In theory I am finding that this is given as a solution but practically I am finding that a user would need execute permission on the stored procedure, select permission on the tables and insert permission on Table2.
sql-server stored-procedures
asked 2 hours ago
user1930901user1930901
494
If you are finding more than just permissions to execute on the proc are needed, it means the ownership chain is broken. Are the objects in the same database and schema?
– Dan Guzman
1 hour ago
add a comment |
I am a bit confused on the permissions required to execute stored procedure. For example, if you have 2 tables and a stored procedure which reads data from Table1 and inserts data into Table2, will execute permission on the stored procedure and select permission on the tables be enough? In theory I am finding that this is given as a solution but practically I am finding that a user would need execute permission on the stored procedure, select permission on the tables and insert permission on Table2.
sql-server stored-procedures
asked 2 hours ago
user1930901user1930901
494
I am a bit confused on the permissions required to execute stored procedure. For example, if you have 2 tables and a stored procedure which reads data from Table1 and inserts data into Table2, will execute permission on the stored procedure and select permission on the tables be enough? In theory I am finding that this is given as a solution but practically I am finding that a user would need execute permission on the stored procedure, select permission on the tables and insert permission on Table2.
sql-server stored-procedures
sql-server stored-procedures
asked 2 hours ago
user1930901user1930901
494
asked 2 hours ago
user1930901user1930901
494
asked 2 hours ago
user1930901user1930901
494
asked 2 hours ago
user1930901user1930901
494
asked 2 hours ago
user1930901user1930901
494
494
If you are finding more than just permissions to execute on the proc are needed, it means the ownership chain is broken. Are the objects in the same database and schema?
– Dan Guzman
1 hour ago
add a comment |
If you are finding more than just permissions to execute on the proc are needed, it means the ownership chain is broken. Are the objects in the same database and schema?
– Dan Guzman
1 hour ago
If you are finding more than just permissions to execute on the proc are needed, it means the ownership chain is broken. Are the objects in the same database and schema?
– Dan Guzman
1 hour ago
If you are finding more than just permissions to execute on the proc are needed, it means the ownership chain is broken. Are the objects in the same database and schema?
– Dan Guzman
1 hour ago
add a comment |
1 Answer
1
active
oldest
votes
No, you don't need to grant explicit permission on Table1 and Table2, that's one of the objective of embedding code in stored procedure and that's where encapsulation feature comes into effect.
Please check below link from Microsoft:
https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/sql/managing-permissions-with-stored-procedures-in-sql-server
Stored Procedure Execution
Stored procedures take advantage of ownership chaining to provide access to data so that users do not need to have explicit permission to access database objects. An ownership chain exists when objects that access each other sequentially are owned by the same user. For example, a stored procedure can call other stored procedures, or a stored procedure can access multiple tables. If all objects in the chain of execution have the same owner, then SQL Server only checks the EXECUTE permission for the caller, not the caller's permissions on other objects. Therefore you need to grant only EXECUTE permissions on stored procedures; you can revoke or deny all permissions on the underlying tables.
use below code to grant execute permission:
USE database_name
go
GRANT EXECUTE ON USP_NAME
TO User_name;
GO
Hope above helps.
answered 1 hour ago
Learning_DBAdminLearning_DBAdmin
695215
add a comment |
Your Answer
StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "182"
;
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: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
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%2fdba.stackexchange.com%2fquestions%2f236876%2fexecuting-a-stored-procedure-which-selects-and-inserts-into-tables-in-sql-server%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
No, you don't need to grant explicit permission on Table1 and Table2, that's one of the objective of embedding code in stored procedure and that's where encapsulation feature comes into effect.
Please check below link from Microsoft:
https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/sql/managing-permissions-with-stored-procedures-in-sql-server
Stored Procedure Execution
Stored procedures take advantage of ownership chaining to provide access to data so that users do not need to have explicit permission to access database objects. An ownership chain exists when objects that access each other sequentially are owned by the same user. For example, a stored procedure can call other stored procedures, or a stored procedure can access multiple tables. If all objects in the chain of execution have the same owner, then SQL Server only checks the EXECUTE permission for the caller, not the caller's permissions on other objects. Therefore you need to grant only EXECUTE permissions on stored procedures; you can revoke or deny all permissions on the underlying tables.
use below code to grant execute permission:
USE database_name
go
GRANT EXECUTE ON USP_NAME
TO User_name;
GO
Hope above helps.
answered 1 hour ago
Learning_DBAdminLearning_DBAdmin
695215
add a comment |
No, you don't need to grant explicit permission on Table1 and Table2, that's one of the objective of embedding code in stored procedure and that's where encapsulation feature comes into effect.
Please check below link from Microsoft:
https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/sql/managing-permissions-with-stored-procedures-in-sql-server
Stored Procedure Execution
Stored procedures take advantage of ownership chaining to provide access to data so that users do not need to have explicit permission to access database objects. An ownership chain exists when objects that access each other sequentially are owned by the same user. For example, a stored procedure can call other stored procedures, or a stored procedure can access multiple tables. If all objects in the chain of execution have the same owner, then SQL Server only checks the EXECUTE permission for the caller, not the caller's permissions on other objects. Therefore you need to grant only EXECUTE permissions on stored procedures; you can revoke or deny all permissions on the underlying tables.
use below code to grant execute permission:
USE database_name
go
GRANT EXECUTE ON USP_NAME
TO User_name;
GO
Hope above helps.
answered 1 hour ago
Learning_DBAdminLearning_DBAdmin
695215
add a comment |
No, you don't need to grant explicit permission on Table1 and Table2, that's one of the objective of embedding code in stored procedure and that's where encapsulation feature comes into effect.
Please check below link from Microsoft:
https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/sql/managing-permissions-with-stored-procedures-in-sql-server
Stored Procedure Execution
Stored procedures take advantage of ownership chaining to provide access to data so that users do not need to have explicit permission to access database objects. An ownership chain exists when objects that access each other sequentially are owned by the same user. For example, a stored procedure can call other stored procedures, or a stored procedure can access multiple tables. If all objects in the chain of execution have the same owner, then SQL Server only checks the EXECUTE permission for the caller, not the caller's permissions on other objects. Therefore you need to grant only EXECUTE permissions on stored procedures; you can revoke or deny all permissions on the underlying tables.
use below code to grant execute permission:
USE database_name
go
GRANT EXECUTE ON USP_NAME
TO User_name;
GO
Hope above helps.
answered 1 hour ago
Learning_DBAdminLearning_DBAdmin
695215
No, you don't need to grant explicit permission on Table1 and Table2, that's one of the objective of embedding code in stored procedure and that's where encapsulation feature comes into effect.
Please check below link from Microsoft:
https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/sql/managing-permissions-with-stored-procedures-in-sql-server
Stored Procedure Execution
Stored procedures take advantage of ownership chaining to provide access to data so that users do not need to have explicit permission to access database objects. An ownership chain exists when objects that access each other sequentially are owned by the same user. For example, a stored procedure can call other stored procedures, or a stored procedure can access multiple tables. If all objects in the chain of execution have the same owner, then SQL Server only checks the EXECUTE permission for the caller, not the caller's permissions on other objects. Therefore you need to grant only EXECUTE permissions on stored procedures; you can revoke or deny all permissions on the underlying tables.
use below code to grant execute permission:
USE database_name
go
GRANT EXECUTE ON USP_NAME
TO User_name;
GO
Hope above helps.
answered 1 hour ago
Learning_DBAdminLearning_DBAdmin
695215
answered 1 hour ago
Learning_DBAdminLearning_DBAdmin
695215
answered 1 hour ago
Learning_DBAdminLearning_DBAdmin
695215
answered 1 hour ago
Learning_DBAdminLearning_DBAdmin
695215
695215
add a comment |
add a comment |
Thanks for contributing an answer to Database Administrators Stack Exchange!
- 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%2fdba.stackexchange.com%2fquestions%2f236876%2fexecuting-a-stored-procedure-which-selects-and-inserts-into-tables-in-sql-server%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
If you are finding more than just permissions to execute on the proc are needed, it means the ownership chain is broken. Are the objects in the same database and schema?
– Dan Guzman
1 hour ago