System Administrator Was Locked Out from SQL Server Instance After InstallationPosted: March 29, 2017
Installed SQL Server Database Engine with a service account (not the admin user) and after installation finished, you cannot login to the installed instance with windows authentication.
How to allow the user (system administrator role) to access the instance?
Here are the steps:
When your start SQL Server in single-user mode with either -m or -f options, any member of computer’s local admins can then connect to the instance as a member of the sysadmin fixed server role.
Option 1: Powershell to run SQLcmd to add the login to sysadmin role (tested):
# stop database service if it is running net stop mssqlserver #start database service as single user mode (for default instance mssqlserve) net start mssqlserver /m # in single user mode, window admin group member can access the instance & sqlcmd -S "myservername" -E -Q "EXEC sp_addsrvrolemember 'myDomain\myID', 'sysadmin';" ##After add the user, restart service without single user mode; net stop mssqlserver net start mssqlserver ## start agent service default instance net start "SQL Server Agent (MSSQLSERVER)" ## to drop a login, not need here. & sqlcmd -S "myservername" -E -q "DROP LOGIN [myDomain\OtherID];"
Option 2 (tested)
2.1 From UI:
Open SQL Server Configuration Manager (run as admin if asked)>> Select SQL Server Services>>Click on your instance to open Properties window>>From Startup Parameters>> Specify a startup parameter
Add -m ( this is a dash and a lower case letter m). >>Click OK and Restart.
Make suer SQL Server Agent Service stopped.
Restart SSMS by Run as administrator and you will be in as a sysadmin now. Create a new login for your account and add your login as sysadmin role from SSMS.
2.2 Or using T-SQL script:
CREATE LOGIN [yourDomain\yourUsername] FROM WINDOWS; ALTER SERVER ROLE sysadmin ADD MEMBER [yourDomain\yourUsername];
Close SSMS and restart SQL Server Configuration Manager and remove the startup parameter -m.
Restart all services now.
Launch SSMS under your user name (windows authentication), you should be connected now.