T-SQL to Get Fixed Drives Free Space in Percentage - ms-sqlserver

use master

go

sp_configure 'Ole Automation Procedures', 1

reconfigure

go

SET NOCOUNT ON

DECLARE @hr int

DECLARE @fso int

DECLARE @drive char(1)

DECLARE @odrive int

DECLARE @TotalSize varchar(20) DECLARE @MB Numeric ; SET @MB = 1048576

CREATE TABLE #drives (drive char(1) PRIMARY KEY,

FreeSpace float NULL,

TotalSize float NULL)

INSERT #drives(drive,FreeSpace)

EXEC master.dbo.xp_fixeddrives

EXEC @hr=sp_OACreate 'Scripting.FileSystemObject',@fso OUT

IF @hr <> 0 EXEC sp_OAGetErrorInfo @fso

DECLARE dcur CURSOR LOCAL FAST_FORWARD

FOR SELECT drive from #drives ORDER by drive

OPEN dcur FETCH NEXT FROM dcur INTO @drive

WHILE @@FETCH_STATUS=0

BEGIN

EXEC @hr = sp_OAMethod @fso,'GetDrive', @odrive OUT, @drive

IF @hr <> 0 EXEC sp_OAGetErrorInfo @fso

EXEC @hr =sp_OAGetProperty @odrive,'TotalSize', @TotalSize OUT

IF @hr <> 0 EXEC sp_OAGetErrorInfo @odrive

UPDATE #drives SET TotalSize=@TotalSize/@MB

WHERE drive=@drive FETCH NEXT FROM dcur INTO @drive

End

Close dcur

DEALLOCATE dcur

EXEC @hr=sp_OADestroy @fso IF @hr <> 0 EXEC sp_OAGetErrorInfo @fso

SELECT drive+':', CAST(CAST((FreeSpace/(TotalSize*1.0))*100.0 as int) as VARCHAR(MAX))+'%' as 'Free(%)' FROM #drives

ORDER BY drive DROP TABLE #drives Return

go

sp_configure 'Ole Automation Procedures', 0

reconfigure

go

Stored Procedure To find all users and roles on a Single/Individual Database in SQL Server 2000

Using this Stored Procedure you can get all the users in an SQL Server Instance. This Has been modified for use with SQL Server 2000 Only.

USE [master]

GO

--CHECK IF THE STORED PROCEDURE ALREADY EXISTS

IF  EXISTS

      (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'[dbo].[spGetUsers]') AND type in (N'P', N'PC'))

      DROP PROCEDURE [dbo].[spGetUsers]

GO

--NAME OF THE DATABASE ON WHICH THE STORED PROCEDURE SHOULD BE CREATED.

--GOOD TO CREATE IT ON MASTER

USE [master]

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

--CREATION OF STORED PROCEDURE

CREATE PROCEDURE [dbo].[spGetUsers]

AS

SET NOCOUNT ON

--VARIABLES DECLARATION

DECLARE @Count INTEGER

DECLARE @varDBName VARCHAR(50)

DECLARE @varDBid INT

DECLARE @varSQL NVARCHAR(4000)

DECLARE @login varchar(50)

DECLARE @getlogin CURSOR

--TEMPORARY TABLE TO HOLD DATABASE AND USER-DEFIINE ROLE USER NAMES

CREATE TABLE #UserDetail

(

      varServerName VARCHAR(255) DEFAULT @@SERVERNAME

      ,varDBName VARCHAR(255)

      ,varDBid INT

      ,varRoleName VARCHAR(255)

      ,varUserName VARCHAR(255)

      ,varUserID VARBINARY(255)

      ,Active CHAR(3)

)

--TEMPORARY TABLE TO DISPLAY DATA

CREATE TABLE #UserDetailDisplay

(

      varServerName VARCHAR(255) DEFAULT @@SERVERNAME

      ,varDBName VARCHAR(255)

      ,varDBid INT

      ,varRoleName VARCHAR(255)

      ,varUserName VARCHAR(255)

      ,varUserID VARBINARY(255)

      ,Active CHAR(3)

)

--TEMPORARY TABLE TO HOLD DATABASE NAMES

CREATE TABLE #DBNameDet

(

      CountID INTEGER IDENTITY(1,1)

      ,varDBName VARCHAR(50)

      ,varDBid INT

)

--TEMPORARY TABLE TO HOLD SP_WHO2 RESULTS

CREATE TABLE #spwho2

(

      spid INT

      ,status CHAR(50)

      ,login VARCHAR(255)

      ,hostname VARCHAR(50)

      ,blkby VARCHAR(50)

      ,dbname VARCHAR(255)

      ,command CHAR(200)

      ,cputime INT

      ,diskio INT

      ,lastbatch VARCHAR(255)

      ,programname VARCHAR(255)

      ,spid1 INT

      --,requestid INT

)

--OBTAIN MEMBERS OF EACH SERVER ROLE

INSERT INTO #UserDetail (varRoleName, varUserName, varUserID)

EXEC dbo.sp_helpsrvrolemember

--OBTAIN DATABASE NAMES

INSERT INTO #DBNameDet (varDBName,varDBid)

SELECT name,dbid FROM MASTER.DBO.SYSDATABASES

--IF you want to exclude any database include where else comment the same.

where name not in ('','','')

      SET @Count = @@ROWCOUNT

--LOOP THROUGH DATABASES TO OBTAIN MEMBERS  OF DATABASE ROLES AND USER-DEFINED ROLES

WHILE @Count > 0

BEGIN

      --GET DATABASE NAME FROM TEMPORARY TABLE

      SET @varDBName = (SELECT varDBName FROM #DBNameDet WHERE CountID = @Count)

      SET @varDBid = (SELECT varDBid FROM #DBNameDet WHERE CountID = @Count)

      --OBTAIN MEMBERS OF EACH DATABASE AND USER-DEFINED ROLE

      SET @varSQL = 'INSERT INTO #UserDetail (varRoleName, varUserName, varUserID)

      EXEC ' + @varDBName + '.dbo.sp_helprolemember'

      EXEC sp_executesql @varSQL

      --UPDATE DATABASE NAME IN TEMPORARY TABLE

      UPDATE #UserDetail

      SET varDBName = @varDBName

      ,varDBid = @varDBid

      WHERE varDBName IS NULL

      SET @Count = @Count - 1

END

--TEMPORARY TABLE TO GET VALUES FROM SP_WHO2

INSERT INTO #spwho2

(

      SPID

      ,Status,Login

      ,HostName

      ,BlkBy

      ,DBName

      ,Command

      ,CPUTime

      ,DiskIO

      ,LastBatch

      ,ProgramName

      ,SPID1

     -- ,REQUESTID

)

EXEC dbo.sp_who2

--CURSUR TO FETCH OPEN USER WHO ARE CURRENTLY CONNECTED TO DATABASES

SET @getlogin = CURSOR FOR

SELECT a.login FROM

#spwho2 a

INNER JOIN #UserDetail t

      ON a.DBname = t.varDBName and a.login = t.varUserName

OPEN @getlogin

FETCH NEXT

FROM @getlogin INTO @login

WHILE @@FETCH_STATUS = 0

BEGIN

      UPDATE #UserDetail SET Active = 'YES' where varUserName in(SELECT a.login FROM

      #spwho2 a

      INNER JOIN #UserDetail t

      ON a.DBname = t.varDBName and a.login = t.varUserName)

      FETCH NEXT

      FROM @getlogin INTO @login

END

CLOSE @getlogin

DEALLOCATE @getlogin

UPDATE #UserDetail set active = 'NO' where active IS NULL

--INSERT DATA INTO TEMPORARY TABLE TO DISPLAY

INSERT INTO #UserDetailDisplay

SELECT tgus.* FROM #UserDetail tgus

LEFT JOIN #UserDetailDisplay tdus

ON tgus.varUserName = tdus.varUserName

AND tgus.varDBName = tdus.varDBName

AND tgus.varRoleName = tdus.varRoleName

AND tgus.varServerName = tdus.varServerName

WHERE tdus.varServerName IS NULL

--SELECT THE RECORDS BY USER--YOU CAN CHANGE AS PER YOUR CONVINIENCE

PRINT 'Display by User'

SELECT varUserName as UserName, varDBName as DBName, varRoleName as RoleName, varServerName as ServerName,Active FROM #UserDetailDisplay

WHERE varUserName<>'dbo'

ORDER BY varUserName

--DROPPING THE TEMPORARY TABLE CREATED

DROP TABLE #UserDetailDisplay

DROP TABLE #UserDetail

DROP TABLE #spwho2

DROP TABLE #DBNameDet

GO

Authenticate Issue in SQL Server due to SID

Found this to be useful so sharing the same.

User is unable to access the database even though his account is existing in the Group and having relevant access.

Cause:

Before Moving to one of the cause let me take you through how authenticate process is done for authenticating to SQL Server using a Domain Account

Process of SQL Authentication:

Whenever a Domain User is logged on to a Machine his SID is being cached in the Machine. When this User Authenticates with SQL Server the below mentioned process is carried out for Authentication.

1. Domain User ID and Password along with SID for Domain Account are passed in.
   
2. The User ID and Passwords along with SID received are Authenticated with the User ID and Password along with SID which is existing on SQL Server.
   
3. When all there are verified successfully the respective existing access is granted to that particular user.
   

Why the problem is occurred:

Basically the groups in Windows can be classified as Domain Global and Domain Local.

The existing Domain Groups may on Domain either of the Hierarchy i.e. Domain Global and Domain Local.  These Domain Group if dropped and Same Domain groups were created at Domain Local / Global Hierarchy then u will come across the issue for sure if this activity is done only at windows level but not at SQL Server.

Now here is where the tricky Part Lies.

Whenever a Domain Account or Group is dropped and Re-Created a New SID is assigned to that Login.

So when this New Login with same name is trying to authenticate with SQL Server the Authentication fails because only the User ID and Password match with that of existing in SQL Server but not the SID because At windows level it is having the new SID and at SQL Server the Old SID which one is Prior to the New Windows login is created.

To resolve the Issue the existing Group/Logins on SQL Server Needs to be dropped and Re-Created so that the New SID gets Synced and Updated and the Authentication can be done successfully.

You can get the Invalid Logins using Stored Procedure sp_validatelogins. But again this is not trust worthy because if an it checks only for SID Matching. Suppose if a Login is dropped and created again this will not show correct results as it compares only SID.

Stored Procedure To find all users and roles on a Single/Individual Database

-- ======================================================================================

-- Author Name: Manjunath C Bhat

-- Author EMail ID: manjunathcbhat@gmail.com

-- Author Designation: Senior Database Engineer

-- Stored Procedure Purpose: To find all users and roles on a Single/Individual Database

-- Stored Procedure Name: spGetDBUsers

-- Parameter1: @DatabaseName
-- ======================================================================================

-- Stored Procedure To find all users and roles on a Single/Individual Database
-- ======================================================================================

USE [YOURDATABASENAME]

GO

--CHECK IF THE STORED PROCEDURE ALREADY EXISTS

IF  EXISTS

      (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[spGetDBUsers]') AND type in (N'P', N'PC'))

      DROP PROCEDURE [dbo].[spGetDBUsers]

GO

--NAME OF THE DATABASE ON WHICH THE STORED PROCEDURE SHOULD BE CREATED.

--GOOD TO CREATE IT ON MASTER

USE [YOURDATABASENAME]

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

--CREATION OF STORED PROCEDURE

CREATE PROCEDURE [dbo].[spGetDBUsers]

@DatabaseName VARCHAR(255)

AS

SET NOCOUNT ON

--VARIABLES DECLARATION

DECLARE @Count INTEGER

DECLARE @varDBName VARCHAR(50)

DECLARE @varDBid INT

DECLARE @varSQL NVARCHAR(4000)

DECLARE @login varchar(50)

DECLARE @getlogin CURSOR

--TEMPORARY TABLE TO HOLD DATABASE AND USER-DEFIINE ROLE USER NAMES

CREATE TABLE #UserDetail

(

      varServerName VARCHAR(255) DEFAULT @@SERVERNAME

      ,varDBName VARCHAR(255)

      ,varDBid INT

      ,varRoleName VARCHAR(255)

      ,varUserName VARCHAR(255)

      ,varUserID VARBINARY(MAX)

      ,Active CHAR(3)

)

--TEMPORARY TABLE TO DISPLAY DATA

CREATE TABLE #UserDetailDisplay

(

      varServerName VARCHAR(255) DEFAULT @@SERVERNAME

      ,varDBName VARCHAR(255)

      ,varDBid INT

      ,varRoleName VARCHAR(255)

      ,varUserName VARCHAR(255)

      ,varUserID VARBINARY(MAX)

      ,Active CHAR(3)

)

--TEMPORARY TABLE TO HOLD DATABASE NAMES

CREATE TABLE #DBNameDet

(

      CountID INTEGER IDENTITY(1,1)

      ,varDBName VARCHAR(50)

      ,varDBid INT

)

--TEMPORARY TABLE TO HOLD SP_WHO2 RESULTS

CREATE TABLE #spwho2

(

      spid INT

      ,status CHAR(50)

      ,login VARCHAR(255)

      ,hostname VARCHAR(50)

      ,blkby VARCHAR(50)

      ,dbname VARCHAR(255)

      ,command CHAR(200)

      ,cputime INT

      ,diskio INT

      ,lastbatch VARCHAR(255)

      ,programname VARCHAR(255)

      ,spid1 INT

      ,requestid INT

)

--OBTAIN MEMBERS OF EACH SERVER ROLE

INSERT INTO #UserDetail (varRoleName, varUserName, varUserID)

EXEC dbo.sp_helpsrvrolemember

--OBTAIN DATABASE NAMES

INSERT INTO #DBNameDet (varDBName,varDBid)

SELECT name,dbid FROM MASTER.DBO.SYSDATABASES WHERE name = @DatabaseName 

--LOOP THROUGH DATABASES TO OBTAIN MEMBERS  OF DATABASE ROLES AND USER-DEFINED ROLES

BEGIN

      --GET DATABASE NAME FROM TEMPORARY TABLE

      SET @varDBName = (SELECT varDBName FROM #DBNameDet)

      --OBTAIN MEMBERS OF EACH DATABASE AND USER-DEFINED ROLE

      SET @varSQL = 'INSERT INTO #UserDetail (varRoleName, varUserName, varUserID)

      EXEC ' + @varDBName + '.dbo.sp_helprolemember'

      EXEC sp_executesql @varSQL

      --UPDATE DATABASE NAME IN TEMPORARY TABLE

      UPDATE #UserDetail

      SET varDBName = @varDBName

      ,varDBid = @varDBid

      WHERE varDBName IS NULL

END

--TEMPORARY TABLE TO GET VALUES FROM SP_WHO2

INSERT INTO #spwho2

(

      SPID

      ,Status,Login

      ,HostName

      ,BlkBy

      ,DBName

      ,Command

      ,CPUTime

      ,DiskIO

      ,LastBatch

      ,ProgramName

      ,SPID1

      ,REQUESTID

)

EXEC dbo.sp_who2

--CURSUR TO FETCH OPEN USER WHO ARE CURRENTLY CONNECTED TO DATABASES

SET @getlogin = CURSOR FOR

SELECT a.login FROM

#spwho2 a

INNER JOIN #UserDetail t

      ON a.DBname = t.varDBName and a.login = t.varUserName

OPEN @getlogin

FETCH NEXT

FROM @getlogin INTO @login

WHILE @@FETCH_STATUS = 0

BEGIN

      UPDATE #UserDetail SET Active = 'YES' where varUserName in(SELECT a.login FROM

      #spwho2 a

      INNER JOIN #UserDetail t

      ON a.DBname = t.varDBName and a.login = t.varUserName)

      FETCH NEXT

      FROM @getlogin INTO @login

END

CLOSE @getlogin

DEALLOCATE @getlogin

UPDATE #UserDetail set active = 'NO' where active IS NULL

--INSERT DATA INTO TEMPORARY TABLE TO DISPLAY

INSERT INTO #UserDetailDisplay

SELECT tgus.* FROM #UserDetail tgus

LEFT JOIN #UserDetailDisplay tdus

ON tgus.varUserName = tdus.varUserName

AND tgus.varDBName = tdus.varDBName

AND tgus.varRoleName = tdus.varRoleName

AND tgus.varServerName = tdus.varServerName

WHERE tdus.varServerName IS NULL

--SELECT THE RECORDS BY USER--YOU CAN CHANGE AS PER YOUR CONVINIENCE

PRINT 'Display by User'

SELECT varUserName as UserName, varDBName as DBName, varRoleName as RoleName, varServerName as ServerName,Active FROM #UserDetailDisplay

WHERE varUserName <> 'dbo'

ORDER BY varUserName

--DROPPING THE TEMPORARY TABLE CREATED

DROP TABLE #UserDetailDisplay

DROP TABLE #UserDetail

DROP TABLE #spwho2

DROP TABLE #DBNameDet

GO

Stored Procedure to Monitor Database and Powershell Script to Monitor Database and Blocking

-- ==========================================================
-- Author Name: Manjunath C Bhat
-- Author EMail ID: manjunathcbhat@gmail.com
-- Author Designation: Senior Database Engineer
-- Stored Procedure Purpose: To Monitor the Database Activity
-- Stored Procedure Name: SQLActivityMonitor
-- Parameter1: @MaxBlockWaitTime Type Int
-- Parameter2: @StatusCode Type TinyInt OUTPUT
-- Parameter3: @StatusMessage Type Varchar OUTPUT
-- ==========================================================
-- Stored Procedure To Monitor SQLDatabaseActivity
-- ==========================================================
-- Drop stored procedure if it already exists
IF EXISTS (
SELECT *
FROM INFORMATION_SCHEMA.ROUTINES
WHERE SPECIFIC_SCHEMA = N'Dbo'
AND SPECIFIC_NAME = N'SQLActivityMonitor'
)
DROP PROCEDURE [DBO].[SQLActivityMonitor]
GO
CREATE PROCEDURE [DBO].[SQLActivityMonitor]
@MaxBlockWaitTime INT,
@StatusCode TINYINT OUTPUT,
@StatusMessage VARCHAR(8000) OUTPUT
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
--Determine # of Active Connections, Open Transactions and Blocked Transaction
DECLARE @BlockedCount INT
DECLARE @OpenTranCount INT
DECLARE @ConnectionCount INT
DECLARE @ExceptionBlockedCount INT
SELECT @ConnectionCount=COUNT(*) FROM MASTER..SYSPROCESSES
SELECT @OpenTranCount = SUM(OPEN_TRAN) FROM MASTER..SYSPROCESSES
SELECT @BlockedCount=COUNT(*),
@ExceptionBlockedCount = SUM(CASE WHEN WAITTIME>@MaxBlockWaitTime THEN 1 ELSE 0 END)
FROM MASTER..SYSPROCESSES
WHERE BLOCKED<>0
IF @ExceptionBlockedCount>0
BEGIN
--Failure if blocked transactions are present that exceeded Timeout
SET @StatusCode=3
SET @StatusMessage = CAST(@ExceptionBlockedCount AS VARCHAR(255)) + ' Blocked transactions (with wait time > specified timeout) are present.'
END
ELSE
BEGIN
IF @BlockedCount>0
BEGIN
--Warn regarding blocked transactions
SET @StatusCode=2
SET @StatusMessage = CAST(@BlockedCount AS VARCHAR(255)) + ' Blocked transactions were present.'
END
ELSE
BEGIN
--OK
SET @StatusCode=1
SET @StatusMessage = 'OK.'
END
END
--Return Key counters
DECLARE @Counters TABLE (Counter VARCHAR(50), [Value] INT, OrderBy TINYINT)
INSERT INTO @Counters (Counter, [Value], OrderBy) VALUES('# Conns', COALESCE(@ConnectionCount,0), 1)
INSERT INTO @Counters (Counter, [Value], OrderBy) VALUES('# Open Trans', COALESCE(@OpenTranCount,0), 2)
INSERT INTO @Counters (Counter, [Value], OrderBy) VALUES('# Blocked', COALESCE(@BlockedCount,0), 3)
INSERT INTO @Counters (Counter, [Value], OrderBy) VALUES('# Block Exceptions', COALESCE(@ExceptionBlockedCount,0), 4)
SELECT COUNTER, [Value]
FROM @Counters
ORDER BY OrderBy
END
GO
-- ==========================================================
-- HOW TO RUN THE PROCEDURE
-- ==========================================================

DECLARE @SCode TINYINT,
@SMessage VARCHAR(8000)
EXECUTE [dbo].[SQLActivityMonitor]
@MaxBlockWaitTime = '15000',
@StatusCode = @SCode OUTPUT,
@StatusMessage = @SMessage OUTPUT
SELECT StatusCode = @SCode,StatusMessage = @SMessage
GO

The OutPut Values can be Retrived in FrontEnd application like Powershell,C# etc.
Below is a sample for using the above Procedure and Retrieving the values in Powershell.
Note: Change the ServerName,DatabaseName and MBwaittime as per your convinience.
Also make a note that the MBWaittime is considered in terms of MilliSeconds.

--POWERSHELL SCRIPT TO MONITOR SQL Database.

$ServerName = 'YOURSERVERNAME'
$DatabaseName = 'YourDatabaseName'
$Query = 'SQLActivityMonitor'
$MBWaittime = 'YourDesiredBlockTime'

$connString = "Server=$serverName;Database=$databaseName;Integrated Security=SSPI;"

$conn = new-object System.Data.SqlClient.SqlConnection $connString
$conn.Open()
$cmd = new-object System.Data.SqlClient.SqlCommand("$Query", $conn)
$cmd.CommandType = [System.Data.CommandType]"StoredProcedure"
$cmd.Parameters.Add("@MaxBlockWaitTime", [System.Data.SqlDbType]"Int")
$cmd.Parameters["@MaxBlockWaitTime"].Value = $MBWaittime
$cmd.Parameters.Add("@StatusCode", [System.Data.SqlDbType]"TinyInt")
$cmd.Parameters["@StatusCode"].Direction = [System.Data.ParameterDirection]"Output"
$cmd.Parameters.Add("@StatusMessage", [System.Data.SqlDbType]"Varchar",8000)
$cmd.Parameters["@StatusMessage"].Direction = [System.Data.ParameterDirection]"Output"
$cmd.ExecuteNonQuery()
$conn.Close()
$cmd.Parameters["@StatusCode"].Value
$cmd.Parameters["@StatusMessage"].Value