Pretty much every MS SQL installation will sooner or later need to have multiple people able to access it with different permissions depending on what role they’re performing for that server. You could create a user in MS SQL itself for every user but that gets difficult to manage quickly. An easier way is to use the existing rights management capabilities of Active Directory or, if not part of a domain, the local system.
- Create security groups in either Active Directory for each separate access level you want to provide. If you are not on a domain, or don’t have the necessary domain privileges to create groups, you can create local groups but the more you can centralize the easier it will be to manage.
- You should probably have at least a group for sysadmin level access but groups for every server role is probably not a bad idea.
- If you have multiple SQL servers, make groups for each server, as well as groups for global access to all servers. You can make the global groups members of the each server-specific group so that you don’t have to add DBAs to dozen’s of groups every time you need to change access
- Connect to your SQL server using SQL Management Studio as user with “sa” privileges (the built-in sa user will work).
- Expand the Security folder in the Object Explorer; right click on “Logins” and select “New Login…”
- On the new login dialog, “Windows Authentication” should be checked by default. Clikc the “Search” button to open the normal user selection dialogue.
- You’ll need to change the “Object Type” to group and add the group(s) you created above (here, it’s a local group named sql_sa). Click OK.
- Switch to the “Server Roles” page and check the appropriate privilege level (sysadmin in this example). Click OK.
You should now be able to manage access to your SQL server using windows authentication instead of have to create SQL user accounts. You can also use this method to control access to specific databases as well.