SCRIPT : To Get Disk Utilization Report

Recently I was working on a request to get a report on disk utilization for about 100 servers.
For sure I don’t want to login to each server and check the disk usage manually. This can be done using T-SQL, but still I was looking for a consolidated report which will give me a clear picture.
Powershell is the best to get this done.
Here is the script I wrote to get the consolidated disk usage report.

===================================================
#Set the path where the html report will be created
Set-Location D:\Powershell\
$a = "<style>"
$b = "<H2>Disk Usage Report</H2>"
$a = $a + "BODY{background-color:Cyan;}"
$a = $a + "TABLE{border-width: 1px;border-style: solid;border-color: black;border-collapse: collapse;}"
$a = $a + "TH{border-width: 2px;padding: 1px;border-style: solid;border-color: black;background-color:thistle}"
$a = $a + "TD{border-width: 2px;padding: 1px;border-style: solid;border-color: black;background-color:palegoldenrod}"
$a = $a + "</style>"
#Place the list of servers for which report is required and update the text file path accordingly
$ServerList = Get-Content "D:\Powershell\serverlist.txt"
Foreach($ServerLists in $ServerList)
{
Get-WMIObject Win32_LogicalDisk -ComputerName $ServerList | Select-Object PSComputerName,DeviceID, DriveType, `
@{name='FreeSpaceInGB';Expression={[math]::truncate($_.freespace / 1GB)}}, `
@{Name='SizeInGB';Expression={[math]::Truncate($_.Size/1Gb)}}, `
@{Name="FreePercentage";Expression={"{0,6:P0}" -f(($_.freespace/1gb) / ($_.size/1gb))}} | Where-Object DriveType -EQ 3 `
| convertto-html PSComputerName,DeviceID,FreeSpaceInGB,sizeInGB, FreePercentage -head $a -Body $b | Out-File Report.htm
}
===================================================

Cheers,
Naveen

How to change MySQL port

In this post we will see how to change MySQL instance port.
By default MySQL will be listening to port 3306. From security perspective it’s not a good idea to run your instance with default port. To change the port all you have to do is edit the configuration file.

Windows -> my.ini
Linux -> my.cnf

How to locate your configuration file?

In windows
-Open services.msc console. Look for your MySQL service
-Right click MySQL service and open ‘Properties’.
-Under ‘Path to execute’ you can find the my.ini location

"C:\Program Files\MySQL\MySQL Server 5.6\bin\mysqld.exe" --defaults-file="C:\ProgramData\MySQL\MySQL Server 5.6\my.ini" MySQL56

-Open the configuration file and search for port, by default its 3306

-I have changed it to 3333.

You have to restart MySQL service to make the changes effective.
Let’s check if MySQL instance is listening to 3333 port. To check the port you can use below command from MySQL client.

SHOW VARIABLES WHERE VARIABLE_NAME LIKE 'PORT';

In Linux

Configuration will be located in /etc/mysql/my.cnf. You have to open and follow the same process.

Note: Take backup of configuration file before making any changes

Cheers,
Naveen

How to check MySQL version in Windows

In this post I will show you different ways to get the version of currently installed MySQL in your machine.

-Using mysqladmin, run the below command (highlighted in red) in cmd

D:\>mysqladmin -u root -p version

-using MySQL client

-Using MySQL service properties (path in service properties in taken installation directory).

-Using MySQL version query

Select @@version (or) Select version()

 

Cheers,
Naveen

How to open MySQL session from any location in Windows

In this blog post I will show you how to open a MySQL session from any location using command prompt.
By default if you try to open a session, you will get below message.

The same command works from the bin directory. In my case path is <C:\Program Files\MySQL\MySQL Server 5.6\bin>.

The solution for this is simple, just go ahead and add bin directory path in system variable path.  Follow the below steps to make the entry

1) Go to ‘Computer’ properties and Select ‘Advanced system settings’.
2) Navigate to ‘Advanced’ tab and select ‘Environment Variables’.

3) Under system variables, look for ‘Path’ variable and click ‘edit’.

4) Now at the end of existing variable value use a semicolon (;) and without leaving space paste the bin directory path with a backslash (\) at the end.

I have highlighted (in red) the path below which was pasted in my case.

…..Doctrine extensions for PHP\;C:\Program Files\MySQL\MySQL Server 5.6\bin\

That’s it we are done. By adding this path we will be able to connect from any location. Open command prompt and now you can run MySQL session from any location. I tried it from D drive this time and it worked :-)

Hope this helps

Cheers,
Naveen

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