Executing a stored procedure which selects and inserts into tables in SQL Server





.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty{ margin-bottom:0;
}







1















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







share|improve this question























  • 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




















1















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







share|improve this question























  • 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
















1












1








1








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







share|improve this question














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






share|improve this question













share|improve this question











share|improve this question




share|improve this question










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





















  • 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












1 Answer
1






active

oldest

votes


















2














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.






share|improve this answer
























    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
    });


    }
    });














    draft saved

    draft discarded


















    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









    2














    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.






    share|improve this answer




























      2














      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.






      share|improve this answer


























        2












        2








        2







        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.






        share|improve this answer













        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.







        share|improve this answer












        share|improve this answer



        share|improve this answer










        answered 1 hour ago









        Learning_DBAdminLearning_DBAdmin

        695215




        695215






























            draft saved

            draft discarded




















































            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.




            draft saved


            draft discarded














            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





















































            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







            -

            Popular posts from this blog

            Fluorita

            Image
            Fluorita Fluorita (blau) amb pirita (daurat). Fórmula química CaF 2 Epònim fluor Localitat tipus Jáchymov Classificació Categoria Halurs Nickel-Strunz 10a ed. 3.AB.25 Nickel-Strunz 9a ed. 3.AB.25 Nickel-Strunz 8a ed. III/A.08 Dana 9.2.1.1 Heys 8.4.7 Propietats Sistema cristal·lí Cúbic Hàbit cristal·lí Sol presentar cubs; menys freqüentment dodecaedres. De vegades hexaoctaedres i tetrahexaedres. Per la combinació d'aquestes formes, les arestes dels cubs són sovint modificades. De vegades els cristalls poden presentar diferències de creixement entre les cares. Sovint s'observen cristalls compostos per sobrecreixement. Pot ser massiva, compacta, terrosa, columnar (poc freqüent), globular en agregats o botroïdal. Estructura cristal·lina a = 5.4626Å Simetria Classe (H-M): m 3 m (4/ m 3 2/ m ) - Hexoctaèdric; Grup espacial: Fm 3 m Color Blanc, groc, verd, violeta, vermell, rosa, blau o negre Mac...
            Read more

            Hulsita

            Image
            Hulsita Hulsita de la localitat tipus Fórmula química Fe 2+ 2 Fe 3+ O 2 BO 3 Epònim Alfred Hulse Brooks Localitat tipus mont Brooks, península de Seward, Nome Borough, Alaska, Estats Units Classificació Categoria borats Nickel-Strunz 10a ed. 6.AB.45 Nickel-Strunz 9a ed. 6.AB.45 Nickel-Strunz 8a ed. V/G.03 Dana 24.2.3.1 Heys 9.6.4 Propietats Sistema cristal·lí monoclínic Estructura cristal·lina a = 10,68Å; b = 3,09Å; c = 5,43Å; β = 94,15° Color negre Macles en [001]; un membre de la macla es troba girat 120° en relació a l'altre Exfoliació bona en {110} Duresa 3 Lluïssor vítria, submetàl·lica Densitat 4,28 g/cm 3 (mesurada); Més informació Estatus IMA mineral heretat (G) i mineral heretat (G) Any d'aprovació 1908 Referències [1] La hulsita és un mineral de la classe dels borats, que pertany al grup de la pinakiolita. Rep el seu nom en honor del nord-americà ...
            Read more

            Península de Txukotka

            Image
            Península de Txukotka Tipus Península Ubicació 66° N, 172° O  /  66°N , 172°O  / 66; -172 Mar mar dels Txuktxis La península de Txukotka a l'extrem oriental de Sibèria La península de Txukotka o península dels Txuktxis (en rus Чуко́тский полуо́стров , Txukotski poluóstrov ) és una península que es troba a l'extrem oriental d'Euràsia. Contingut 1 Descripció 2 Població 3 Vegeu també 4 Bibliografia Descripció L'estret de Bering, entre la península de Txukotka i la península de Seward a Alaska Foto de satèl·lit de la zona amb la península de Txukotka a l'esquerra El seu punt més extrem és el cap Dezhnev a la vora del nucli d'Uelen, un dels poquíssims llocs habitats de la zona. Al nord limita amb el mar dels Txuktxis i amb el mar de Bering al sud i amb l'estret de Bering a l'est. A la costa sud es troben la badia de Sant Lawrence i la badia de Kresta. La penínsu...
            Read more