Enable normal user to execute a sys stored procedure

  • SZ

    SSC Veteran

    Points: 273

    I tried

    GRANT EXECUTE ON OBJECT :: sys.sp.droprolemember

    TO John;

    GO

    Cannot find the object 'droprolemember', because it does not exist or you do not have permission.

    ???

    Tried

    GRANT EXECUTE ON sys.sp_droprolemember TO John

    but get

    Msg 4629, Level 16, State 10, Line 1

    Permissions on server scoped catalog views or system stored procedures or extended stored procedures can be granted only when the current database is master.

    • This topic was modified 1 month ago by  SZ.
  • Mr. Brian Gale

    SSC-Insane

    Points: 23075

    I think this may be by design.  A normal user generally shouldn't need to run any sys stored procedures as those are more aimed at a DBA.

    If you REALLY need them to be run by a normal end user, my recommendation would be to make a new stored procedure that is executed as the database owner that calls the system stored procedure and then grant permissions on that stored procedure to the regular user.

    For me though, I would not want a regular end user to be able to do things like drop role members as that is more of an administrative task and as such, I would want a DBA to handle that.  Or at the very least, the person responsible for that database (ie the database owner).

    Alternately, I believe you could give a user "ALTER ANY ROLE" permissions which would allow them to run ALTER ROLE <rolename> DROP USER <username> which would be effectively the same thing as sp_droprolemember. NOTE - this won't work for SQL Data Warehouse or Parallel Data Warehouse.

     

    ALTER ROLE - https://docs.microsoft.com/en-us/sql/t-sql/statements/alter-role-transact-sql?view=sql-server-ver15

    PERMISSIONS - https://docs.microsoft.com/en-us/sql/relational-databases/security/permissions-database-engine?view=sql-server-ver15

  • Jeff Moden

    SSC Guru

    Points: 996843

    To be blunt, there is no way in hell I'd give a "normal" user the privs to do anything with roles indirectly never mind directly.  If such functionality is required, the write a stored procedure to do it, have it execute as the "dbowner", and give the user the privs to execute the stored procedure making sure that the stored procedure is well written and the user cannot overstep their bounds.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    "If "pre-optimization" is the root of all evil, then what does the resulting no optimization lead to?"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 720484

    I'd actually write a job to do this. I'd allow a user to put a name into a table somewhere. I'd have a job that looked in this table, and if this wasn't any admin, it would run the role drop itself, logging the action.

     

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply