Enable normal user to execute a sys stored procedure

  • I tried

    GRANT EXECUTE ON OBJECT :: sys.sp.droprolemember

    TO John;


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



    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 3 years, 10 months ago by  SZ.
  • 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

    The above is all just my opinion on what you should do. 
    As with all advice you find on a random internet forum - you shouldn't blindly follow it.  Always test on a test server to see if there is negative side effects before making changes to live!
    I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.

  • 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.

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

  • 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 3 (of 3 total)

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