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]
How to creep the reader out with what seems like a normal person?
Don’t seats that recline flat defeat the purpose of having seatbelts?
How come there are so many candidates for the 2020 Democratic party presidential nomination?
Unexpected email from Yorkshire Bank
Can solid acids and bases have pH values? If not, how are they classified as acids or bases?
Do vanished people know what happened after the snap?
A Note on N!
Why the difference in metal between 銀行 and お金?
Does this extra sentence in the description of the warlock's Eyes of the Rune Keeper eldritch invocation appear in any official reference?
How to back up a running remote server?
How do Bards prepare spells?
Is it possible to measure lightning discharges as Nikola Tesla?
Trainer for recumbent bikes
How to verbalise code in Mathematica?
How to pronounce 'C++' in Spanish
How could Tony Stark make this in Endgame?
Python: pythonic way to find last position in string that does not match regex
Question relating to a number theoretic function
How would one muzzle a full grown polar bear in the 13th century?
Will this character get back his Infinity Stone?
Why do 401k up to company match, then fill Roth IRA, then finish filling 401k?
Are Boeing 737-800’s grounded?
Is DC-to-DC (24 V to 12 V) buck conversion typically more efficient than AC-to-DC (110 V to 12 V) conversion?
Has any spacecraft ever had the ability to directly communicate with civilian air traffic control?
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