How to add a sql administrator if you find yourself locked out.
(replace the server name and service names with your own!)
1. Stop the SQL Service -
Microsoft Windows [Version 6.3.9600]
(c) 2013 Microsoft Corporation. All rights reserved.
C:\Users\Administrator>net stop mssql$sql2012
The following services are dependent on the SQL Server (SQL2012) service.
Stopping the SQL Server (SQL2012) service will also stop these services.
SQL Server Agent (SQL2012)
Do you want to continue this operation? (Y/N) [N]: Y
The SQL Server Agent (SQL2012) service is stopping.
The SQL Server Agent (SQL2012) service was stopped successfully.
The SQL Server (SQL2012) service is stopping.
The SQL Server (SQL2012) service was stopped successfully.
2. Start the SQL Service in single user mode -
C:\Users\Administrator>net start mssql$sql2012 /m
The SQL Server (SQL2012) service is starting.
The SQL Server (SQL2012) service was started successfully.
3. Add a user -
C:\Users\Administrator>osql -E -Sxeros\sql2012
1> CREATE LOGIN mylogin WITH PASSWORD = 'mypassword' , CHECK_POLICY=OFF;
2> GO
1> ALTER SERVER ROLE sysadmin ADD MEMBER mylogin;
2> GO
1> EXIT
4. Stop the Sql service (you'ved finished with single user mode)
C:\Users\Administrator>net stop mssql$sql2012
The SQL Server (SQL2012) service is stopping.
The SQL Server (SQL2012) service was stopped successfully.
5. Start the service fully again
C:\Users\Administrator>net start mssql$sql2012
The SQL Server (SQL2012) service is starting.
The SQL Server (SQL2012) service was started successfully.
2 comments:
You can actually fix this without any downtime in most or many cases. See this for example: http://www.mssqltips.com/sqlservertip/2682/recover-access-to-a-sql-server-instance/
Thank you very much for pointing me to that James, will have to give it a go.
You can teach an old dog new tricks it would appear :)
Admittedly the single user mode & restart is old news but I found myself doing it again so I figured I'd put some notes up.
Post a Comment