Reducing the SQL installation users permissions

Question

Situation:  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?

Answer 1

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 :-)

Answer 2

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.

Answer 3

Please let me know if there are more questions I can answer to resolve this issue.

Answer 4

Hi kirtrik,

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.

 

Thanks,
Maggie

---

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.

Answer 5

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'.