Posts from the ‘SQL Server Security’ Category

May 18

How to Recover sa password

If you have forgot your ‘sa’ account password and do not have any other account with admin privileges available. Also as a best practice you ave removed the access from builtin\Admin account also. How you will connect to SQL Server instance?

You can follow below steps to recover/reset ‘sa’ account password:-

1) Login to SQL Server box with account having admin privileges on the box.

2) Go to SQL Server configuration manager and stop the SQL Server service for which you want to reset ‘sa’ password.

3) Right click on SQL Server service –> Properties –> go to advanced tab.

4) Under the advanced panel go to Startup Parameters option and add ;-m at the end of the current parameter values, without any spaces. -m option is used to start SQL Server in single user mode.

5) Restart SQL Server. This will start SQL Server in single user mode.

6) Open SQL Server management studio and open new query window. Here connect to the instance with windows authentication. SQL Server will allow you to connect as admin.

Note:- When you start SQL Server in single user mode and are connected to box with admin rights on physical server, SQL Server allows your windows account to act as a admin to SQL Server instance also.

7) Now create a new temporary account with sysadmin privilige.

create user temp with password = 'Temp123';

go

exec sp_addsrvrolemember 'temp,'sysadmin';

go

or, if you already have a account to which you want to grant sysadmin access, you can do that also.

8) Stop SQL Server service and remove ;-m from startup parameters.

9) Connect to SQL Server with the account we have created at step 7.

10) Now change your ‘sa’ account password and then connect using your new ‘sa’ account password.

Please note this will work for SQL Server 2005, SQL Server 2008 and SQL Server 2008 R2. These steps will not work on SQL Server 2000 and are not tested on SQL Server 2012. If you have forgot ‘sa’ password for SQL Server 2012 instance, its worth trying to see if this works or not.

Aug 18

SQL Server Logins and Passwords – Tips

Here are some of the scripts that may help you with logins and passwords in SQL Server 2005 and higher versions.

Script 1: -

If you want to check if any SQL Server login is having password same as login name. You can use the below script.

select name from syslogins where pwdcompare(name,password)=1;

Script 2: -

You can check SQL Server logins with no password by using below script.

select name from sys.sql_logins where pwdcompare(”,password_hash)=1;

Script 3: -

You can check SQL Server logins with commonly used passwords by using below script.

select name from sys.sql_logins

where pwdcompare(‘password’,password_hash)=1

or pwdcompare(‘test’,password_hash)=1

or pwdcompare(‘Password’,password_hash)=1

or pwdcompare(‘Test’,password_hash)=1

or pwdcompare(”,password_hash)=1;

Script 4: -

Use the below script to check for SQL Server logins having high level access and password same as login name.

select name from syslogins where pwdcompare(name,password)=1 and

(sysadmin=1 or securityadmin=1 or serveradmin=1 or setupadmin=1

or processadmin=1 or diskadmin=1 or dbcreator=1 or bulkadmin=1);

Script 5: -

You can use the below script to check for SQL Server logins having high-level access and having commonly used passwords or blank password.

select SL.name from sys.sql_logins SL, syslogins L

where SL.name=L.name and

(sysadmin=1 or securityadmin=1 or serveradmin=1 or setupadmin=1

or processadmin=1 or diskadmin=1 or dbcreator=1 or bulkadmin=1)

and

(pwdcompare(‘password’,password_hash)=1

or pwdcompare(‘test’,password_hash)=1

or pwdcompare(‘Password’,password_hash)=1

or pwdcompare(‘Test’,password_hash)=1

or pwdcompare(”,password_hash)=1);

Where some of the best practices in DBA world are shared…..

Posts from the ‘SQL Server Security’ Category

How to Recover sa password

Recent Posts

Categories

Archives

Where some of the best practices in DBA world are shared…..

Posts from the ‘SQL Server Security’ Category

How to Recover sa password

SQL Server Logins and Passwords – Tips

Subscribe

Recent Posts

Categories

Archives