August 27, 2013 at 9:51 am
Hi Friends,
Let us assume I have 2 logins XXX & YYY.
XXX-->sysadmin
YYY-->all databases reader permission.
XXX is trying to change the permission for YYY login to sysadmin or db_owner for all databases. Is there a way to restrict the access instead of removing the sysadmin privilege for XXX login.
Thanks in advance..
August 28, 2013 at 1:25 am
Hi Grasshopper,
you can make new database role, depending on your specifications. Set your XXX user to that role
August 28, 2013 at 1:30 am
So you don't want XXX to be able to change YYY's permissions? Start by removing XXX from sysadmin. What do you want XXX to be able to do?
John
August 28, 2013 at 1:39 am
There is nothing you can do to stop a sysadmin from doing whatever he wants. If you add a trigger to prevent him from changing a login, he can drop or disable the trigger, make the change and re-enable the trigger.
If you have someone who shouldn't have sysadmin-level access,then they shouldn't have the sysadmin role.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply