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.
