none
Grant a user rights to create "select only" stored procedure

    Question

  • Hi all,

    I need to create a database user which can access tables (SELECT only) and create stored procedure (containing only SELECT statement).
    The user should be able to grant EXECUTE permissions on procedures to others users.

    But a user to create a procedure must be member of db_owner, so he has all rights on tables (including INSERT, etc.).

    How can I solve?

    Thank you all!
    Friday, May 3, 2019 9:11 AM

Answers

All replies

  • But a user to create a procedure must be member of db_owner
    No, it's enough when the user is member of the db_dlladmin database role.

    Olaf Helper

    [ Blog] [ Xing] [ MVP]


    • Edited by Olaf HelperMVP Friday, May 3, 2019 9:23 AM
    • Marked as answer by Nazza Monday, May 6, 2019 9:10 AM
    Friday, May 3, 2019 9:23 AM
  • But a user to create a procedure must be member of db_owner, so he has all rights on tables (including INSERT, etc.).

    As Olaf points out, db_owner is not needed. In order to create stored procedures you need CREATE PROCEDURE permission on database level and ALTER permission on the schema.

    However, there is no provision to control what's inside the stored procedure. Once a user can create procedure, it can do anything. Or more precisely, it works this way (and for simplicity I will assume that dbo owns all tables):

    * If the schema where the user creates the procedure is owned by dbo, dbo becomes the owner of the procedure, and thus there is ownership chaing and no access control. If the procedure deletes data, the user can delete data.

    * If the procedure is not owned by dbo, there will be permission checks, so that when users runs the procedure, they will need DELETE permission on that table.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Friday, May 3, 2019 9:32 PM
  • Hmmm. in addition, why then allow  the user to create procedures?, just GRANT SELECT to the user  , that's all

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Sunday, May 5, 2019 5:11 AM
  • Hi Olaf, Erland and Uri,

    i found a solution using your suggestions.

    The user is member of db_ddladmin role, so he can create stored procedures (in his own schema).

    He can create any type of stored (he can use INSERT, UDATE, etc), but giving only SELECT rights on tables, he can successfullt execute only those containing SELECT.

    Thank you all.

    Monday, May 6, 2019 9:15 AM