SQL Server Performance Forum – Threads Archive
problems revoking sysadm permissions
HiI have a problem with a windows user having sysadm permssions when he shouldnt
I had removed sysadm from the builtin admins group which worked for existing admins
However I think 2 new admins were added to the machine after that, and they some how still achieved sysadm status.
I have since removed them from the box admin group – no change,
added them back in, given sysadm back to the builtins then removed it – no change,
explicitly added them as an individual user and disabled the account – which worked
(but that doesnt really help cos they need access but not sysadm, when I reenabled them they could still do sysadm stuff)
I have even gone so far as to delete the builtin admins group – no change
Has anyone come accross this before or know how I can force this user down to only either ddl or dbo on one database only?
Thanks in advance
Rebekah
How those 2 new admin logins are added?
Are they part of local Administrator group on thsi server? What version of SQL you are using Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
@http://www.askasqlguru.com/ This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.
Under what account sql service is running?
If it is running under domain account check they are the member of the domain admin group and domain admin group is member of local adming group…
MohammedU.
Microsoft SQL Server MVP
Moderator
SQL-Server-Performance.com All postings are provided “AS IS†with no warranties for accuracy.
The sql service is running as a domain user who is box admin, but the problem user wouldnt be a domain admin and was only briefly a box admin
The offending account I believe was an nt account that was added as an admin to the box after I had unticked that builtin admins sysadmin permissions. When I initially revoked sysadmin from builtins it stoped existing builtins from being able to do sysadm tasks, but others added since then could still do sysadm tasks. I have also since upgraded from 2005sp1 to sp2 and new users added to builtins no longer have this issue.
I am just not sure what to do with the two users who can get in with sysadm privs – apart from completely locking them out
Rebekah
See this blog post is any help here.
]]>