Revoke "EXEC sp_password" to a certain user?

[JFoushee]

Hello.

Can I revoke the sp_password stored procedure from a specific user?

Right now, the permissions of sp_password are set to nothing for guest and EXEC for public. Clicking on "List all users" doesn't change the list beyond guest and public.

I tried to run this script, but it didn't help either:

REVOKE EXEC ON [sp_password]
TO [my_user]

comes back with:
There is no such user or group 'my_user'.

Yet this user is in the Logins area.

 

[osjohnm]

Hi

sp_password resides in the master database. If the login you are trying to deny access to doesn't have access to the master database then you will receive that error since it doesn't have permissions on the master database in the first place.

execute permissions on sp_password default to the public role and sysadmin role. A sql user may execute sp_password but they can only change their own password.

Only someone who has sysadmin rights can execute sp_password to change another login's password.

Even if you try and deny execute to the logins directly it still won't work since public has execute permissions and all sql logins can't exist without being mapped to the public role.

Therefore you must deny execute to the public database role:

DENY EXECUTE ON sp_password
TO public

That should do it.

John

 

[SQLSister]

Check the database Users section and see if he is listed. If not add him to the list, then set his permissions by denying himm the rights to the stored procedure (and anything else he shouldn't have access to!). He is getting here because Public has permission to Exec and he is probably a member of public role. By the way if this guy can't be trusted to change his own password (or if he is maliciously changing other people's passwords), you need to look really closely at his permissions throughout all your databases. Hopefully he doesn't have sysadmin rights! If you think he knows the sa password, I'd change that too. He could be getting in through this route.