Pages

Thursday, July 29, 2010

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

Tuesday, July 27, 2010

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.

Wednesday, July 14, 2010

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

Wednesday, July 7, 2010

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