In this guide i’ll show how to grant a Active Directory user access to SQL Server with sysadmin privileges.
We have 2 servers – Dev01 running Active Directory Domain Server (AD DS) and Dev02 running SQL Server.
I would like to grant a specific user on the domain sysadmin access to the SQL Server.
Open ssms (SQL Management Studio) on Dev02 and login as administrator (Windows Authentication) or with the build in sa account, depending on what settings you did chose when installing SQL Server.
Expand the Security folder, right click on “Logins” and chose “New Login”
Click “Search” under login name and “Advanced” in the new window that’ll appear
Chose “Entire Directory” instead of machine name (DEV02), to search the domain. Click “OK”
Click “Find Now” to get a list of all available users. Choose the user you want to grant access, and click “OK”
Right click the newly created user and choose “Properties”. Go to the “Server Roles” tab.
Check “sysadmin” and click “OK”.
The user is now granted sysadmin rights to the SQL Server.