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

Always good to know your Guest (SQL Guest User)

Few days back came across an interesting issue, which was new for me. Application owner buzzed me and raised concerns on security issue.

App.Owner: We have some serious issue with our database security. I fear wrong people having elevated access to our data.
Me: Ok, let me have quick look and get back to you.
App.Owner: Yes please. We need to fix it now.
Me: Did a quick check and everything seems to be fine. You’re ID with db_owner and other users with read access to the database.
App.Owner: I was also under the same impression. But today came to other users were also are also able to modify the data. That’s not at all good.
Me: hmm that’s strange. I will do a complete check to figure out the leakage.
App.Owner: Thanks. I will wait to hear from you.
Me: Okay. Ahh Found the suspect. All those privileges were getting transmitted from guest user. I could see someone had mistakenly provided extra permission to guest user, which is not at all required for this environment. Now things are back to normal. Please have a look from your end and let me know you if you have any questions
App.Owner: Great! Thanks for fixing this. Have a nice day
Me: You too, have a nice day.

In my case issue was with guest user permission. Let’s see the same in demo

-First we will create a new database named ‘TESTDB’. Then will check the status of guest account.

Note: If guest user is enabled in Model database, the same will get applied to newly created database

-Now we will create a SQL Login, a new table ‘EPL’ and insert 3 records.

We have just created a SQL Login it’s not mapped to any of the database and instance level also no permission is provided.

Now let’s try accessing the data in TESTDB using UserA.

As expected, ‘UserA’ is not able to fetch the data from TESTDB. Now let’s try enabling and providing read and write permission to ‘Guest’ user in TESTDB. You cannot enable/disable Guest user through GUI, it can be done only with T-SQL.

To Enable Guest Account:
 
USE [DATABASE_NAME]
GO
GRANT CONNECT TO GUEST
GO
 
To Disable Guest Account:

USE [DATABASE_NAME]
GO
REVOKE CONNECT TO GUEST
GO

Command completed successfully. Now let’s try

 
 So now UserA is able to read and insert records in TESTDB. The same task can be performed by all users associated with the instance. Guest user when enabled will authorize access for logins which are not mapped to the database.

Things to know about Guest user

• It’s always recommended to disable guest user for user database, unless you have any specific/special requirement.
• Guest user cannot be dropped, but can be disabled/enabled.
• Guest user cannot be disabled for Master and Tempdb database.
• Msdb database guest user should not be disabled. If you have any plans to do so, please read this.

Cheers,
Naveen

Automating Standalone SQL Installation

Few days back I started working on Automating or Standardizing SQL Installation. Ultimate goal is to reduce the manual work. Unattended installation using configuration file will help us to achieve this. 

First let's see how to get our configuration file. First time you have to run the setup manually and pass all the parameters.  You have move till “Ready to install” page in the installation wizard. There you will get the configurationfile.ini location.

Copy the configurationfile.ini to the location where SQL setup file is placed (It’s not mandatory to do this. If you place the configuration file in different location, same location should be passed while executing the setup).

So now your configuration file is ready. Make the below changes in configuration file to perform unattended installation.

   -Change QUIET="False" to QUIET="True"

 

   -Remove UIMODE="Normal" from configurationfile.ini

Now save and close the file. Please note, configuration file will not store/have password details of any of the accounts (SQL Service Account, SQL Agent Account and SA Password etc.). So we have to pass this information explicitly while running setup in command line. And also we should pass a parameter /IACCEPTSQLSERVERLICENSETERMS=”True” to accept the SQL Software license.

Below is the script to do unattended installation.

***********************************************************************************

setup.exe /ConfigurationFile=ConfigurationFile.ini /IACCEPTSQLSERVERLICENSETERMS=”True” /SAPWD=”SaAccountPassword” /SQLSVCPASSWORD=”SQLServiceAccountPassword” /AGTSVCPASSWORD=”AgentAccountPassword”

***********************************************************************************

Note: SA password is not required for Windows Authentication

You can create a batch file and run it to perform unattended SQL installation. This will be advantageous if you have to in setup SQL in multiple servers with same configuration.

But what if you have a requirement to install SQL in multiple server with different instance name and collation settings?

Above script will not work in that case. However if you could go and make required changes to the configuration file every time will work. But again that will be time consuming task to make changes in all servers. Other option would be passing the parameters explicitly while running the setup. So here also lot of editing will be required.

To overcome all these difficulties, I wrote a small dynamic script where we have to define few parameters based on our requirement and those parameters will be taken as input while running the setup.

I have just added below parameters in my script to be taken as input.

      -SQL Instance Name

       -SQL Collation

       -SQL Service Account Name and Password

       -SQL Agent Account Name and Password

       -SA Account password

       - To accept SQL Server License Terms

Assuming in most of the case install directory, root instance directory and other directory will be standard. So initially whatever you have passed in the configuration file will be taken.

You may need to change the below script according to your requirement.

***********************************************************************************

@echo off

color 02

echo===========================================

echo Unattended SQL Installation

echo===========================================

color 05

set /p LI=Do You Accept SQL Server License Terms(True/False):

set /p IN=Enter SQL Instance Name:

set /p SYS=Enter Sysadmin Account Name:

set /p SA=Enter SA Account Password:

set /p SVN=Enter SQL Service Account Name:

set /p SVP=Enter SQL Service Account Password:

set /p AGN=Enter SLQ Agent Account Name:

set /p AGP=Enter SQL Agent Account Password:

set /p COL=Enter SQL Collation:

color 09

echo===========================================

Echo Starting SQL Server Installation....

echo===========================================

date/t

time/t

setup.exe /ConfigurationFile=ConfigurationFile.ini /IACCEPTSQLSERVERLICENSETERMS=%LI% /INSTANCENAME=%IN% /INSTANCEID=%IN% /SQLSYSADMINACCOUNTS=%SYS% /SQLCOLLATION=%COL% /SAPWD=%SA% /SQLSVCACCOUNT=%SVN% /SQLSVCPASSWORD=%SVP% /AGTSVCACCOUNT=%AGN% /AGTSVCPASSWORD=%AGP%

color 02

echo Batch file completed

date /t

time /t

***********************************************************************************

Now all you have to do is prepare your configuration file and save the above script in .bat format. Place both the files where SQL Setup is placed.

That’s it, open command prompt with “Run as administrator”, run the batch file, provide required inputs and your SQL installation will start. 

Below is the screenshot of SQL installation in my system.

 

You will get the start and end time of installation. To make it bit sparkling added few colors in between the code.  So on each phase of batch execution you would notice a change in color!!!

To learn more about Unattended SQL Installation click here.

Hope this helps in Automating SQL Installation :-)

Cheers,

Naveen