Central Management of Sql Logins

SQL Server Administration
1

HI,
I
have 4 servers that host multiple databases. I am in process to
implement role/user based security to limit QAs and Dev Access. As we
have dev,test and stage env too, so it make it more trouble to manage
login in in all envs. We don't have domain integration to MSSQl, so I
will need to manage sql logins for all users.e.g. create new user,edit
role ,edit password etc.
I want to manage them centrally to avoid
any inconsistency, create/edit user on one server and it may reflect to
all server. By searching google I found I can use CMS(Central Management
Server) group to manage MSSQL instances, but it use windows
authentication only.
Is this good option to manage logins or I may consider some other tools/factors too ?

Thanks

2

Best thing is to use so-called double-abstraction:

  1. Create AD groups for the your DEV and QA groups
  2. Put the Windows logins into the proper AD group
  3. Create Database roles with the just the permissions required for DEV and QA access
  4. Add the Windows groups to the Server logins and database users
  5. add the groups to the proper database roles

That way, you manage the users at the AD level and never have to mess with them at the SQL level.

1 Like
3

Thank you gbritton.
But what if I don't have AD and associated users ?

I am evaluating solution where I don't have AD, users will connect directly to server with sql logins.

4

Use windows users and groups