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]
Has any spacecraft ever had the ability to directly communicate with civilian air traffic control?
How to delegate to implementing class
Do I have an "anti-research" personality?
Killing undead fish underwater
Please, smoke with good manners
Counterexample: a pair of linearly ordered sets that are isomorphic to subsets of the other, but not isomorphic between them
Why was Germany not as successful as other Europeans in establishing overseas colonies?
Inner for loop when run in background in bash spawns new bash process
How would one muzzle a full grown polar bear in the 13th century?
Was it really necessary for the Lunar module LM to have 2 stages?
Unexpected email from Yorkshire Bank
Short story about a planet with two sentient species
How do I deal with a coworker that keeps asking to make small superficial changes to a report, and it is seriously triggering my anxiety?
Are Boeing 737-800’s grounded?
Was there a shared-world project before "Thieves World"?
What does YCWCYODFTRFDTY mean?
Why does nature favour the Laplacian?
Will a top journal at least read my introduction?
How can I place the product on a social media post better?
How to set the font color of quantity objects (Version 11.3 vs version 12)
Will tsunami waves travel forever if there was no land?
Does this extra sentence in the description of the warlock's Eyes of the Rune Keeper eldritch invocation appear in any official reference?
Smart diagram in Mathematica
How to creep the reader out with what seems like a normal person?
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