You can connect to SQL Server with SQLCMD and perform the following operations to add yourself back as a sysadmin. Once the SQL Server service has been started in single user mode or with a minimal configuration, you can now use the SQLCMD command from a command prompt. ![]() ![]() Note: If the Binn folder is not in your path, you’ll need to change to the Binn folder. ![]() NET START MSSQLSERVER /m OR SQLServr.Exe –m (or SQLServr.exe –f) Start SQL Server 2005 in Single-User mode using the command:.Stop SQL Server 2005 using the command, NET STOP MSSQLSERVER.To use the command prompt to recover your system, use the following steps: Restarting SQL Server using the SQL Command Line: Once the sysadmin access has been recovered, remove the " -m" from the startup parameters using the Configuration Manager and restart the SQL Server Instance.You can use T-SQL commands such as "sp_addsrvrolemember" to add a login to the sysadmin server role.ĮXEC sp_addsrvrolemember 'DomainName\DBAGroupName', 'sysadmin' After the SQL Server Instance starts in single-user mode, the Windows Administrator account is able to connect to SQL Server using the sqlcmd utility and Windows authentication.NOTE: make sure there is no space between " " and "-m".The SQL Server ERRORLOG file will include an entry that says "SQL Server started in single-user mode." Click the "OK" button and restart the SQL Server Instance.In Properties add " –m" to the end of the list in the startup parameters. Navigate to the "Advanced" tab of the properties of the database engine.Stop the SQL Server Instance you need to recover.To use the Configuration Manager tool to recover your system, use the following steps: This provides proper controls for the file access and other privileges. You can use the SQL Server Configuration Manager tool to start SQL Server in single-user mode. Restarting SQL Server Using SQL Server Configuration Manager However, that would mean your Windows account will need to be a member of the Local Windows Administrators group. ![]() There’s a way with which you can gain sysadmin access to your SQL Server. SQL Server 2005 and SQL Server 2008 provide a better disaster recovery option by allowing members of the Local Windows Administrators group to log in with sysadmin access whenever SQL Server is started in single-user mode, also known as "maintenance mode ". However, this could take some time, and it doesn’t guarantee that all your logins, users, permissions, and server configurations will be recovered unless you plan to restore the master database from an old backup. At this point you could rebuild the master database or reinstall SQL Server and attach all the user databases. To regain your sysadmin rights you decided to use SA account but you find out that SA had been disabled on this SQL Server. While performing this security best practice on one of your SQL Server you accidently removed your company’s DBA group from sysadmin fixed server role. It has been recommended by the auditors to follow the Microsoft security best practices by removing the BUILTIN\Administrators and logins other than DBAs from the sysadmin fixed server role. As part of this audit it has been noted that on some of your company’s SQL Servers BUILTIN\Administrators group and logins other than DBAs are members of the sysadmin fixed server role. Let’s pretend a situation where your company’s SQL Servers had been recently audited by external auditors. In this article, I will show you the steps which you require to regain your sysadmin access.
0 Comments
Leave a Reply. |