Enable normal user to execute a sys stored procedure

Question

Enable normal user to execute a sys stored procedure

SZ

Old Hand

Points: 338
More actions
July 7, 2020 at 1:56 am

#3767879
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 3 years, 9 months ago by SZ.

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

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

Mr. Brian Gale SSC-Insane Points: 24670 More actions · Answer 1

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.

Jeff Moden SSC Guru Points: 1003853 More actions · Answer 2

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)

Steve Jones - SSC Editor SSC Guru Points: 734418 More actions · Answer 3

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.