Stored Procedure to CRASH your SQL Instance

Few days back I leanrt about a configuration option in SQL Server called 'scan for startup procs'.
Scan for startup procs option is used to execute stored procedures during SQL Instance startup. By default this option is not enabled. You can enable it using ‘sp_configure’, below is the command. SQL Service needs to be restarted to make 'scan for startup procs' configuration effective. I will also be enabling ‘XP_CMDSHELL’ for this demonstration.

EXEC SP_CONFIGURE 'show advanced options',1
GO
RECONFIGURE
EXEC SP_CONFIGURE 'scan for startup procs',1
GO
RECONFIGURE
EXEC SP_CONFIGURE 'XP_CMDSHELL',1
GO
RECONFIGURE

Now it’s time to do some bad things. Run the below script, which will create a stored procedure named ‘Destroy’ in master database and that will be added to scan for startup procs. This SP will get executed during the SQL Instance startup. 

USE MASTER
GO
CREATE PROCEDURE DESTROY
AS
EXEC XP_CMDSHELL 'NET STOP MSSQL$SQL2012'
GO
EXEC SP_PROCOPTION @PROCNAME = 'DESTROY',
@OPTIONNAME = 'STARTUP',
@OPTIONVALUE = 'ON'

In above script change the SQL instance name according to your instance type.

Hope you know what happens if this gets executed :-)

Now go ahead and stop/start or restart your SQL Instance. You will not be able to start your SQL Instance.  If you look at the error log, you’ll find below messages.

Once the recovery is completed scan for startup procedure is made and SP is executed.

So now how to come out from this situation?
Start SQL in single user mode (/m) and remove this procedure from startup/drop this procedure/disable this configuration.  Let’s have a look at the SQL error log.

We can see only recovery part is completed and no startup procedure is executed.

To check the list of procedures added to startup procedure use below command

SELECT NAME FROM SYS.PROCEDURES WHERE IS_AUTO_EXECUTED=1

 

 

Now we’ll remove this SP from the startup proc by running below command and check the status.

EXEC SP_PROCOPTION @PROCNAME = 'DESTROY', @OPTIONNAME = 'STARTUP', @OPTIONVALUE = 'OFF'

 

 

 

 

 

Cheers,

Naveen

PS Testing this script is at your own risk. Please test/play with this in your test environment.

How to setup database mail with your gmail account

In this blog post let’s see how to setup database mail with your gmail account.
Make sure Database Mail XPs is enabled for your instance. To check the status run the below command

EXEC SP_CONFIGURE 'show advanced options',1
RECONFIGURE
EXEC SP_CONFIGURE 'Database Mail XPs'

In my case it is disabled, let’s go ahead and enable it.

EXEC SP_CONFIGURE 'Database Mail XPs',1
RECONFIGURE
EXEC SP_CONFIGURE 'Database Mail XPs'

Now in object explorer pane
-Expand Management Node
-Right click Database Mail -> click ‘Configure Database Mail’ -> next
-In Select configuration Task, select ‘Set up Database Mail by performing the following task:’

-Enter the profile name and description.

-Now Add SMPT Account to create your database mail account. Few things which you should take care is

• Provide a valid E-mail address, SMTP Server Name and Port.
• For SMTP authentication select ‘Basic authentication’. User name will be your email address and password will be your gmail account password.

Click here, to get more information about Google SMTP Server settings

-Select whichever profile you need (Public/Private profiles available) and if required you can set it to default profile as well.

-Next window will show the system parameters. If necessary you can customize it, I will proceed with default.

-Click next and finish!

You have successfully configured database mail. Now let’s send a test mail to validate. Right click ‘Database Mail’ -> ‘Send Test E-Mail…’. Provide your E-mail ID and click ‘Send Test E-Mail’.

If you have provided a valid/correct email address you'll soon receive a test email.


Cheers,
Naveen

SCRIPT : To take backup of user permiissions, object level permissions and roles

You can use this script to to extract user permissions in a database with object level permission and roles. It is recommended to get the output in text format, so that it will be easy to have a copy.

PRINT 'DATABASE NAME : ' +DB_NAME()

SET NOCOUNT ON

/*CREATE DATABASE ROLES*/

SELECT 'CREATE ROLE [' + a.name + '] AUTHORIZATION [' + b.name + ']' from sys.database_principals a

INNER JOIN sys.database_principals b ON a.owning_principal_id = b.principal_id

where a.type = 'R' and a.is_fixed_role <> 1

GO

------------------------------------------------------------------------------------------------------------------

/*ASSIGN ROLES TO USER*/

SELECT --rm.role_principal_id,

'EXEC sp_addrolemember @rolename ='

+ SPACE(1) + QUOTENAME(USER_NAME(rm.role_principal_id), '''')

+ ', @membername =' + SPACE(1) + QUOTENAME(USER_NAME(rm.member_principal_id), '''') AS '--Role Memberships'

FROM sys.database_role_members AS rm

ORDER BY rm.role_principal_id

GO

-------------------------------------------------------------------------------------------------------------------

/*SELECT OBJECT LEVEL PERMISSION*/

SELECT

CASE WHEN perm.state != 'W' THEN perm.state_desc ELSE 'GRANT' END + SPACE(1) +

perm.permission_name + SPACE(1) + 'ON '+ QUOTENAME(Schema_NAME(obj.schema_id)) + '.'

+ QUOTENAME(obj.name) collate Latin1_General_CI_AS_KS_WS

+ CASE WHEN cl.column_id IS NULL THEN SPACE(0) ELSE '(' + QUOTENAME(cl.name) + ')' END

+ SPACE(1) + 'TO' + SPACE(1) + QUOTENAME(usr.name)

+ CASE WHEN perm.state <> 'W' THEN SPACE(0) ELSE SPACE(1) + 'WITH GRANT OPTION' END AS '--Object Level Permissions'

FROM sys.database_permissions AS perm

INNER JOIN

sys.objects AS obj

ON perm.major_id = obj.[object_id]

INNER JOIN

sys.database_principals AS usr

ON perm.grantee_principal_id = usr.principal_id

LEFT JOIN

sys.columns AS cl

ON cl.column_id = perm.minor_id AND cl.[object_id] = perm.major_id

ORDER BY usr.name

-------------------------------------------------------------------------------------------------------------------

/*SELECT OBJECT LEVEL PERMISSION FOR A OBJECT*/

SELECT

CASE WHEN perm.state != 'W' THEN perm.state_desc ELSE 'GRANT' END + SPACE(1) +

perm.permission_name + SPACE(1) + 'ON '+ QUOTENAME(Schema_NAME(obj.schema_id)) + '.'

+ QUOTENAME(obj.name) collate Latin1_General_CI_AS_KS_WS

+ CASE WHEN cl.column_id IS NULL THEN SPACE(0) ELSE '(' + QUOTENAME(cl.name) + ')' END

+ SPACE(1) + 'TO' + SPACE(1) + QUOTENAME(usr.name)

+ CASE WHEN perm.state <> 'W' THEN SPACE(0) ELSE SPACE(1) + 'WITH GRANT OPTION' END AS '--Object Level Permissions'

FROM sys.database_permissions AS perm

INNER JOIN

sys.objects AS obj

ON perm.major_id = obj.[object_id]

INNER JOIN

sys.database_principals AS usr

ON perm.grantee_principal_id = usr.principal_id

LEFT JOIN

sys.columns AS cl

ON cl.column_id = perm.minor_id AND cl.[object_id] = perm.major_id

Where obj.name = 'ULTIMATES_CURRENT'

ORDER BY usr.name

SET NOCOUNT OFF