Step 1:

Right-click Start > Search > type in ‘ssms.exe’ > right-click Microsoft SQL Server Management Studio > Run as Administrator

Authenticate using an sa account > New Query > paste this line:

/* Adding Principle into SQL sysadmin role */
exec sp_addsrvrolemember 'whatDomain\Domain Admins', 'sysadmin';
go

Observe a result similar to this:

Commands completed successfully.
Completion time: 2012-09-03T18:42:58.1788611-07:00

That’s it. There is no ‘Step 2.’ Fin.

# Alternative Method via PowerShell
$sa='sa'
$saPassword='whatpassword'
$domainAdmins="$env:USERDOMAIN\Domain Admins"

import-module sqlps
try{
    if($sa -and $saPassword){
        invoke-sqlcmd -username $sa -password $saPassword -Query "exec sp_addsrvrolemember '$domainAdmins', 'sysadmin'"
    }else{
        invoke-sqlcmd -Query "exec sp_addsrvrolemember '$domainAdmins', 'sysadmin'"
    }
}catch{
    write-warning $_
}

Alternative Graphical Interface Method:

Run Microsoft SQL Server Management Studio (ssms.exe) > authenticate as an sa > navigate to [Server Named Instance] > Security > right-click Logins > New Logins > set Login Name = YOURDOMAIN\Domain Admins & select ‘Windows authentication’

Click on Server Roles > put a check mark next to ‘sysadmin’ > click OK

Visually verify that ‘Domain Admins’ has been added to the security group

Troubleshooting:
Issue:

A member of the Domain Admins group could not logon to SQL Server even though such group has been granted the ‘sysadmin’ role on such database machine.

Resolution:

By default, Windows User Access Control (UAC) will associate the run-time of an un-elevated instance of SQL Server Management Studio in the context of a regular user, not ‘BUILTIN\Administrators’. To ensure that the GUI is initiated in the Admin contexts, one must right-click SSMS > select Run as Administrator. On then would members of the Domain Admins be granted access.

Categories: