Introduction
SQL Server runs with 2 authentication modes, "Windows Authentication" or "Windows and SQL Server Authentication". Windows Authentication mode, which is the default one, presents a higher access control on the SQL Server instance since it allows only the users listed in the Domain's Active directory, or local users to obtain a connection to a SQL Server instance and its databases.

In this exercises, we will learn how to grant a "sysadmin" SQL privilege on the SQL server instance to a group of windows users.

Steps
01. Login to the computer hosting the SQL Server Instance as "Administrator".

02. Right-click My Computer and then click "Manage".

03. In Computer Management, expand the console tree and select "Local Users and Groups".

04. In "Local Users and Groups", right-click the "Groups" folder and then choose "New Group".  



05. In the Group Name text box, type "SQLAdmins", then click "Add".

06. In the "Select Users" dialog box, type the login name of the windows account you want to add to the Group, and then click "OK". (ex: an account with the name "sql").  



07. In the New Group dialog box, click "Create" and then click "Close".

08. The New Group will show up in the Groups Folder.



09. Open the SQL Server Management Studio (SSMS).

10. Connect to the SQL Server instance.  



11. In SSMS, expand the "Security" folder.

12. Under the "Security" folder, right-click the "Logins" folder and then choose "New Login". 



13. In the "New" dialog box, click "Search". This will open the "Select User Or Group" dialog box.

14. In the dialog box, click "Object Types".

15. In the "Object Types" dialog box, select the "Groups" check box, and then click "OK".  



16. In the "Select User Or Group" dialog box, type "SQLAdmins" group name, then click "OK".  



17. In the "New" dialog box, click "OK".

18. In SSMS, under the "Security" folder, expand the "Server Roles" folder.

19. In the "Server Roles" folder, right-click "sysadmin" role and select "Properties". 



20. In the "Server Role Properties" dialog box, click "Add".

21. In the "Select Logins" dialog box, type "SQLAdmins" and then click "OK".  



22. In the "Objects Found" dialog box, select the check box next to "ComputerName\SQLAdmins" and then click "OK".  



23. In the "Server Login" dialog box click "OK".

24. In the "Server Role Properties" dialog box, click "OK".



Now, when you login to the computer using any of the accounts that have been added to the "SQLAdmins" group (ex: sql), you will find that this account has full access rights and privileges on the SQL Server instance.  



By implementing such a method to provide a certain access control to the SQL Server instance, a complete security policy can be implemented using the Local windows or Domain accounts. Also the User groups created can be created on the Active Directory Domain and be given access to the SQL Instance to provide a more secure methodology across the whole domain. Moreover, you can control access to the SQL Server instance across forest of Multiple Active Directory domains which have all established a trust relationship.