Sql-server – How to switch to sa account from guest session in SQL server 2012

passwordpermissionssql serversql-server-2012

I have a situation where I can log into my server as guest, and see that there is no password on the sa account. However when I try to login as sa with null password over the network it is rejected.

My question is, since there is no sa password, how can I do the equivalent of a unix sudo or su command, while logged in as guest? I looked into runas and changing passwords but guest lacks the very privilege I am trying to gain. ('alter login sa..' doesn't work.)

Also, why does the server refuses login when there is no sa password?

This is SQL Server 2012 SP1 Web edition 64-bit 11.0.3153.0.

For example, there is a command in MySQL called change_user. This command can be used as the name suggests to change a user during a MySQL session. There may be a way to do this in SQL Server but I am not a SQL Server expert so I seek more experienced input.

Best Answer

When signed in under the guest account, you have very few privileges by default. The sa password is displayed as blank because you do not have the necessary rights to view it. Disabling the guest account is a security best practice, where it is not required.

The real sa password is almost certainly not blank. SQL Server does not allow a blank sa password since the 2005 release.

SQL Server does provide a way to impersonate another login or user (assuming you have sufficient rights). See EXECUTE AS (Transact-SQL) for details.

Note that SETUSER is deprecated, provided only for backward compatibility, and has inferior functionality.

Related Solutions

Sql-server – In which form Microsoft SQL password should be given when creating a user

Yes, it is there correctly. You have run CREATE LOGIN correctly.

The number of stars in the SSMS box is just a mask and is unrelated to your actual password. SSMS doesn't know or try to read your password from the server

Note that SQL Server doesn't store your password so it can't relate to the **** in SSMS anyway. SQL Server hashes it into the password_hash column of sys.server_principals

Sql-server – Set SA to weak Password in SQL Server 2012

Try

 ALTER LOGIN sa
 WITH PASSWORD = 'password',
  CHECK_POLICY = OFF

(I feel dirty now)

In order to mitigate some of the potential horrors this allows, I'd recommend renaming the built-in sa account to something else, then creating a new login called sa with the rights the app actually requires.

Best Answer

Related Solutions

Sql-server – In which form Microsoft SQL password should be given when creating a user

Sql-server – Set SA to weak Password in SQL Server 2012

Related Question