Scenario:<\/strong><\/p>\n

\n
1. SQL Server \u2018sa\u2019 or any user id is locked out.<\/li>\n
2. User has no access to SQL Server authenitcation.<\/li>\n
3. Windows Logged in user is part of Windows local Administrators group where the SQL Server is running and has currently logged into the machine.<\/li>\n<\/ol>\n

   Microsoft has removed the functionality of automatically adding the local windows system admin to SQL 2008 Users. The following solutions are workaround to get inside the SQL server by adding a user to SQL Users Table.<\/p>\n

   Note: Since this involves SQLCMD and starting, stopping of SQL Instance the logged in user should be an administrator of that server where the SQL Server is running and both solutions should be run under an elevated command prompt.<\/p>\n

   Solution 1 : (Little difficult Solution. Check Solution 2 for automated script by Microsoft)<\/strong><\/p>\n

   Open command prompt.<\/p>\n

   \n
   • If you have default instance run following command on command prompt to stop SQL service: “net stop mssqlserver”<\/li>\n
   • Now go to the directory where SQL server is installed. In my case the directory is “C:Program FilesMicrosoft SQL ServerMSSQL10_50.MSSQLSERVERMSSQLBinn”. So need to run CD C:Program FilesMicrosoft SQL ServerMSSQL10_50.MSSQLSERVERMSSQLBinn.<\/li>\n
   • Now run the following command to start sql server in single user mode. As we have specified “SQLCMD”, now only SQLCMD connection can be made.<\/li>\n<\/ul>\n

     sqlservr -m”SQLCMD”<\/p><\/blockquote>\n

     \n
     • Now open another command prompt window and write command SQLCMD<\/li>\n
     • Run following two commands<\/li>\n<\/ul>\n

       CREATE LOGIN [testAdmin] WITH PASSWORD=N’test@1234′, DEFAULT_DATABASE=[master];
       \nEXEC sys.sp_addsrvrolemember @loginame = N’testAdmin’, @rolename = N’sysadmin’;
       \nGO<\/p><\/blockquote>\n

       \n
       • Go back to first command window and press Ctrl+C to stop the SQL server and then type ‘Y’ for confirmation. This will stop the sql server.<\/li>\n
       • Start SQL server again and no startup parameters need to specified this time.<\/li>\n
       • Now using SSMS, try to connect with “testAdmin” as user and “test@1234” as password.<\/li>\n
       • Create your own logins.<\/li>\n
       • Drop testAdmin as it is not required any more.<\/li>\n<\/ul>\n

         Note: If you do not have SQL authentication enabled then you can try adding your windows user and replace setp-6 with below queries. Here <<DOMAINUSERNAME>> is placeholder for your user name<\/p>\n

         create login [<<DOMAINUSERNAME>>] from windows;
         \nEXEC sys.sp_addsrvrolemember @loginame = N'<<DOMAINUSERNAME>>’, @rolename = N’sysadmin’;
         \nGO;<\/p><\/blockquote>\n

         Source: http:\/\/beyondrelational.com\/blogs\/chintak\/archive\/2010\/07\/20\/sql-2008-r2-new-installation-and-login-password-unknown.aspx<\/a><\/p>\n

         Solution 2 : (Easy Solution)<\/strong><\/p>\n

         Download the following\u00a0file, extract\u00a0and run the cmd file\u00a0in elevated command prompt.
         \nhttp:\/\/downloads.binarybits.net\/blog\/addselftosqlsysadmin.zip<\/a><\/p>\n

         Input:<\/em>
         \n%1 specifies the instance name to be modified. Defaults to SQLEXPRESS.
         \n%2 specifies the principal identity to be added (in the form “<domain><user>”).
         \nIf omitted, the script will request elevation and add the current user (pre-elevation) to the sysadmin role.
         \nIf provided explicitly, the script is assumed to be running elevated already.<\/p>\n

         Method:<\/em>
         \n1) restart the SQL service with the ‘-m’ option, which allows a single connection from a box admin (the box admin is temporarily added to the sysadmin role with this start option)
         \n2) connect to the SQL instance and add the user to the sysadmin role
         \n3) restart the SQL service for normal connections<\/p>\n

         Output:<\/em>
         \nMessages indicating success\/failure.
         \nNote that if elevation is done by this script, a new command process window is created: the output of this\u00a0window is not directly accessible to the caller.<\/p>\n

         Source: http:\/\/archive.msdn.microsoft.com\/addselftosqlsysadmin\/<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"

         Scenario: SQL Server \u2018sa\u2019 or any user id is locked out. User has no access to SQL Server authenitcation. Windows Logged in user is part of Windows local Administrators group where the SQL Server is running and has currently logged into the machine. Microsoft has removed the functionality of automatically adding the local windows system […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"inline_featured_image":false,"footnotes":""},"categories":[4],"tags":[],"yoast_head":"\n