Issue

You are attempting to log into SQL Management Studio however you can't log in with SQL authentication or Windows authentication.

Cause

This generally occurs when SQL was installed by another person in the past and that user account was associated as an administrator for SQL however the current user account that you are connected on is not authorized for SQL. If you know the user/pass for a SQL user account such as the sa user then you can use that information, however by default mixed-mode authentication (sql authentication) is disabled.

Answer

  • Open a command prompt (run as administrator)
  • Type net stop mssqlserver substitute mssqlserver for the appropriate instance name.
  • Type net start mssqlserver /m. The /m starts SQL in single-user maintenance mode.
  • Open SQL Management Studio
  • Connect to the instance using Windows Authentication
  • Adjust the security settings as needed
    • Change the security mode
    • Create a new sql user account if necessary
    • Reset the password to the sa sql user account, etc.
  • Close SQL Management Studio
  • Type net stop mssqlserver
  • Type net start mssqlserver

Version Information

  • Entered by: Brandon Readlinger
  • Entered date: 8/26/2014


See Also


http://msdn.microsoft.com/en-us/library/ms188236.aspx
http://blogs.technet.com/b/meamcs/archive/2011/12/02/cannot-login-to-sql-server-using-administrator-account.aspx