08 Mart 2012 Perşembe 22:14Situation: I install SQL 2008 R2 with my domain user and set domain user and one other domain user up as a sysadmin during the install. Later I have a need to reduce my permissions to only allow SELECT type statements. I remove my explicitly named SQL domain user and add him to a local Windows usergroup. The local Windows usergroup has been added to SQL and everyone I place into this group has the reduced permissions except for me. To [troubleshoot/narrow this down], I even tried placing the other secondary domain user named as a sysadmin during the install into this group and they also get reduced permissions in SQL. My domain user continues to work with more permissions than I desire them to have. The only difference is that my domain user was the one who also performed the installation of the SQL instance. I have not found any documentation that seems to relate with what I am seeing. What am I missing?
09 Mart 2012 Cuma 09:23Moderatör
Can you run xp_logininfo 'your ID' (eg. xp_logininfo 'Amer\sagar') and post the output.
When you install the setup you need to select the IDs for admin rights. Ensure your ID is added to the low privileged group then remove your individual ID from sql server logins.
Mark as ANSWER if I helped you today :-)
09 Mart 2012 Cuma 19:49
The information you requested (which is the corrent permission diminished group):
NA\Patrick.Paden user user NA\Patrick.Paden KS11WS000033187\SQLoffsite
My user has already been removed from everywhere else otherwise in MSSQL.
The steps I took to reduce my permissions I did with the other user that I had added with my user during installation as a sysadmin.
His user is now working correctly with diminished permissions, while I still have to many.
26 Mart 2012 Pazartesi 20:06Please let me know if there are more questions I can answer to resolve this issue.
27 Mart 2012 Salı 08:43Moderatör
The domain user you put it in a local Windows usergroup may be also a member of Administrators. By default, all members of the Windows BUILTIN\Administrators group, the local administrator's group, are members of the sysadmin fixed server role. And can perform any activity in the server. You need to remove the domain user from the Administrators.
Please remember to mark the replies as answers if they help and unmark them if they provide no help. This can be beneficial to other community members reading the thread.
27 Mart 2012 Salı 15:36
Unfortunately, I had already previously removed that particular user from the BUILTIN\Administrators group. The only Windows groups joined to that login is the 'SQLoffsite' and 'Users'.