1

I'm working on AD permissions and have removed my user account from the Domain Admins security group. Now I'm having difficult accessing my SQL Server database with my Windows login. The login is in the sysadmin server role group and has mapped permissions to databases on the server. Any ideas why I can't access the server itself?

I will say that if I add a security group that my Windows login is a part of and give that group read permissions...I can then access the server.

Any advice would be appreciated.

Thank you!

Improve this question
5
  • 1
    As a very general security practice you should not granting a domain admin account database access. Create a regular account that goes in a group that has sysadmin on the database. Separation of responsibilities...
    – kevinskio
    Jul 24, 2019 at 17:25
  • So you're suggesting I create a new AD Group with sysadmin role, then plop my windows login in that group?
    – Ezeakiel
    Jul 24, 2019 at 17:30
  • That is my recommendation, and you also mention that this works too.
    – kevinskio
    Jul 24, 2019 at 17:51
  • Great. I've added the AD group and added my user account to the group. I've added the DBA group to SQL Servers login and applied the sysadmin role. Now...my windows login is getting this error..."Could not find a login matching the name provided"...any ideas?
    – Ezeakiel
    Jul 24, 2019 at 17:54
  • I restarted SSMS as Administrator and it's now letting me login with my windows login...wonder why that is?
    – Ezeakiel
    Jul 24, 2019 at 17:56

1 Answer 1

Reset to default
-1

Create a regular account that goes in a group that has sysadmin on the database. Then restart your SQL Server management studio and log on using windows authentication. You can start SSMS using the "Run-as" to log on with a different account than you used to log onto your desktop

This gives you Separation of responsibilities between domain admins and database administrators.

Improve this answer
4
  • I removed my user account from the Domain Admins group and now have placed my user account in a DBA group. I now have to run SSMS as administrator in order to access the SQL server...any idea? I'm trying to implement your strategy. I appreciate the advice.
    – Ezeakiel
    Jul 24, 2019 at 18:08
  • @Ezeakiel are you running SSMS from a desktop or the server? Does the user in the DBA group have sysadmin on the database? Or see here serverfault.com/a/852085/122935
    – kevinskio
    Jul 24, 2019 at 18:11
  • Running SSMS from a desktop. User in the DBA group has sysadmin role on the database. I can change the setting that's suggested in the link. But just curious why it's necessary? Something to do with connecting remotely probably?
    – Ezeakiel
    Jul 24, 2019 at 18:15
  • @Ezeakiel the server fault answer indicates a bug, you hoped it would have been fixed but sometimes these things take time
    – kevinskio
    Jul 24, 2019 at 18:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.