Friday 19 April 2013

Access to SQL Server when all administrator accounts are deleted

If you removed Builtin\administrator from SQL Server which is the best security practice, and you forgot SA password, or SA account is disabled and there is no other Windows or SQL Server account on SQL Server with sysadmin privilege, starting SQL Server in single-user mode enables any member of the computer’s local Administrators group to connect to the instance of SQL Server as a member of the sysadmin fixed server role.
1. Stop all SQL Server related services (some SQL services such as SQL Agent will use up your only connection in single-user mode).
2. Start the SQL service with the -m parameter.
In SQL Server Configuration Manager, click SQL Server Services. In the right pane, right-click SQL Server (), and then click Properties. On the Startup Parameters tab, add -m; parameter (do not add a space between the -m; and the rest of the command line).

Or from CMD with ‘Run as administrator’ input:
NET START MSSQLSERVER /m

This will put the SQL into Single User Mode, and SQL Server will only accept one connection.
Connect with SQL Server Management Studio and add login with sysadmin privilege, or use sqlcmd to connect to your server with the -E trusted connection option. You will be able to connect to the SQL Server as sysadmin if you are a local administrator.
Create your login and add it to the sysadmin role.
USE master
GO
CREATE LOGIN [domain\username] FROM WINDOWS WITH DEFAULT_DATABASE=[Master]
GO
EXEC sp_addsrvrolemember @loginame=N'domain\username', @rolename=N'sysadmin'
GO

Stop the SQL service, remove the -m parameter and restart the service. You should now be able to connect to the SQL Server normally.
If you get the error message:

No comments:

Post a Comment