Forgotten sa password

It is frustrating to forget the sa password . Despite maintaining a password vault , it is  possible someone has changed the password or you don’t have access to the password vault. Don’t worry. If you have access to a Local administrator member than this method can help

1) Set the Single-User mode for the SQL Server Instance

go to start->run->type cmd->type services.msc->search  for SQL server (MSSQLserver) name->right click properties->

->under General tab you can see path to executable. Make a note of the sql server executable path  .

 go to start->run->type cmd->type  <sql server executable path> -m s<instance_name>

example if SQL Server Instance is called “Test1”:

 

C:\Program Files\Microsoft SQL Server\MSSQL.3\MSSQL\Binn\sqlserver.exe –m sTest1

 

Note:
a) Stop the SQL Server Agent service before connecting to an instance of SQL Server in single-user mode; otherwise, the SQL Server Agent service uses the connection, thereby blocking it.
b) Using the –s switch avoids the error :  Sql server installation is either corrupt or has been tampered with error getting instance id from name

2) Add a sysadmin account  through SQLCMD

 

EXEC sp_addsrvrolemember 'Org1\Johnny', 'sysadmin';
GO

 

3) Return to multiuser mode for the SQL Server Instance

 Stop the single user-mode session

Start the SQL Server Instance in multiuser mode

 

4) Change sa password

Using either SSMS or SQLCMD , reset the sa password

 

sp_password NULL,'new_password','sa'

 Read More

Find Weak passwords in SQL Server - SQL Server DBA

sp_password and ALTER LOGIN - SQL Server DBA

Account Locked out - SQL Server DBA

Sql server installation is either corrupt or has been tampered with error getting instance id from name

When attempting to start a SQL Server named instance in single user mode using the command line steps below , I receive a "Sql server installation is either corrupt or has been tampered with error getting instance id from name" . Is there a workaround?

Cd C:\Program Files\Microsoft SQL Server\MSSQL.3\MSSQL\Binn>
Sqlservr.exe –m

 

To avoid this error , add the flag    -s<InstanceName> to the string .   If I have a SQL Server Instance called MyInstance ,  the command  should be :

 

Sqlserver.exe –m –sMyInstance

 

Connect to the server through SQLCMD - C:>\sqlcmd -S <sql server name>

Read More

  MULTI – RESTRICTED USER - SQL Server DBA

Drop Connections with ROLLBACK IMMEDIATE to allow a Detach database

Database Security Countermeasures against hacker attacks

 

Find Weak passwords in SQL Server

How can I find weak passwords in SQL Server logins?

Identifying weak passwords is an  important part of Security Risk Analysis. Setting  up a procedure to check for weak passwords should be part of the DBA daily healthcheck.  It is straightforward to check for a weak password using a SELECT statement and the PWDCOMPARE function.

The PWDCOMPARE function accepts 2 arguments – the first is the text password and the varbin value of the SQL password hash

 

--find SQL login with blank passwords
select name,type_desc,create_date from sys.sql_logins where pwdcompare('', password_hash) = 1

--find SQL login with password same as name

select name,type_desc,create_date from sys.sql_logins where pwdcompare(name, password_hash) = 1

 

 

Read More

Powershell sql server security audit - SQL Server DBA

How to create a SQL Server Security Audit - SQL Server DBA

Database Server Security Audit Process - SQL Server DBA

Find who made a database security change - SQL Server DBA

Who dropped the table in SQL Server

SQL Server has a default trace feature – a lightweight background trace capturing Object creation, object deletion, error events, auditing events and full text events. For more information read SQL Server – default trace FAQ

If  a user has deleted a database object , such as a table – as long as the trace log is still available – it is easy to write a query and return the relevant information.

The query returns useful information such as Application Name,LoginName,StartTime,ObjectName,Databasename. Enough information to begin a proper analysis of what events led to the object deleted.

I’ve used this query many times , to investigate tables deleted – either accidentally or intentionally. Don’t forget to change the database name .

Once you discover who dropped the table – review the actions and decide on revoking the drop table privileges.Read more on Database Security Countermeasures against hacker attacks

 

SELECT * 
FROM ::fn_trace_gettable(CONVERT(VARCHAR(150), ( SELECT TOP 1

                                                              f.[value]

                                                      FROM    sys.fn_trace_getinfo(NULL) f

                                                      WHERE   f.property = 2

                                                    )), DEFAULT) tf
INNER JOIN sys.trace_events te
ON eventclass = trace_event_id
INNER JOIN sys.trace_categories AS tc
ON te.category_id = tc.category_id
WHERE databasename = 'MY_DB' AND
objectname IS NOT NULL AND 
te.category_id = 5 AND 
te.trace_event_id = 47  
AND tf.StartTime > getdate() -1

 Read More

How to create a SQL Server Security Audit - SQL Server DBA

Powershell sql server security audit - SQL Server DBA

SQL Server – Last DML operation - SQL Server DBA

List sql database roles

The sys.database_principals view returns the sql database roles. The view returns all the  principles , but using the type = ‘R’ predicate limits the recordset to database roles

I find this query useful when scheduling database moves from different environments. Sometimes  database roles have to be created to allow elevated privileges, as part of a condition I’ll check to for the existence of a database role.

To view members attached to database roles read : SQL Database roles and members

 

--list all database roles 
SELECT  name,type,type_desc,is_fixed_role
FROM sys.database_principals WHERE type = 'R'

--check for a database role , before creating a new database role
use myDB
go
if not exists(SELECT  * 
FROM sys.database_principals WHERE name = 'special_execute')
BEGIN
CREATE ROLE special_execute
GRANT EXECUTE ON sp_a_smape TO [database_user]
END

 

Read More

SQL Database roles and members

Powershell sql server security audit - SQL Server DBA

How to create a SQL Server Security Audit - SQL Server DBA