An Exceptions-Based Report for SQL Backups – Part 2   Leave a comment

As promised, here is part 2 of of the blog “An Exceptions-Based Report for SQL Backups” (click HERE for the first blog).

Using the attached “Backups Exception Report.rdl” file as an example (click HERE to download), you can create a nifty report based on the stored procedure in the first post. Click HERE to see an example in .pdf format of what the report looks like when rendered. The report .RDL file is in SSRS 2005 format, so you can easily use it with SSRS 2005 and above.

One of the most useful – well fantastic, really! – features of this report and the accompanying stored procedure is that records are shown only for SQL backups that are missing. Potential exceptions are denoted in red and bolded. If there are no missing backups for the SQL instance, a message is displayed stating, “No exceptions were noted for this server.” In this way you have a quick and reliable way to identify when your backups are being missed, without having to wade through the vast majority of backups that are not missing.

You will also notice that the report displays records for databases that are in a status other than “ONLINE” (e.g. “OFFLINE” status), with the last backups of each type displayed (assuming one of the last backups ages over the number of prescribed days). This will be a handy visual reminder of any databases in an unusual status.

To use the example .RDL file, load it into an SSRS project in BIDS and open it. When it first opens, there may be an error displayed. Click to ignore it as it is just trying to connect the data source to a dummy SQL server name which I have embedded in the report. Also make sure you deploy the stored procedure from the first post to the master database of the instance you will be running this against.

Once the report is open in BIDS, go to the data sources tab and click to edit the data set. Within that dialog box, click to edit the data source and change the name of it, and the connection properties, to reflect the name of your SQL instance, then click OK. Then make sure the query type is set to Stored Procedure and in the query text field, paste the name of the procedure, rpt_SQL_Backups_Report. Click on the parameters tab and make sure there is a parameter named “@type” (don’t include the double quotes), and in the value column for that, make sure it says:

=”all_exceptions”

…(include the double quotes)

When you click OK, another error may be generated stating it cannot connect to a #TEMP table. This is expected. Click OK, then click the “Refresh Fields” button on the toolbar, and when it prompts you to enter a value for “@type,” enter the string “all_exceptions” (DON’T include the double quotes).

If you made a change to the name of the data set, you will need to point the table on the layout tab to use the new data set name. That is all! You are ready to start using this report.

Another enhancement, which I have not demonstrated here, is to include data sets for multiple SQL instances on the same report, and wire them up to separate tables on the layout tab (copying and pasting from the first table to easily create clones).

Happy reporting!

Posted March 25, 2011 by Norm Enger in Microsoft SQL Server

Advertisements

Posted March 25, 2011 by Norm Enger in Microsoft SQL Server

An Exceptions-Based Report for SQL Backups   Leave a comment

UPDATE 3/26/2011: If you used an earlier version of the stored procedure, I have made a couple of minor corrections to the procedure below, including adding logic for including, in the all_exceptions implementation, dbs in the BULK_LOGGED recovery model, which were omitted before.

Here is a stored procedure that reports on SQL backups that are missing, based on the number of days since the last backups of each type, for each SQL database on an instance. This stored procedure works with all versions of SQL Server, from SQL 2000 and above.

In particular, calling the procedure with the “all_exceptions” parameter will show records for any databases in the SQL instance that have not had a Transaction Log backup in over 1 day (if in the FULL or BULK_LOGGED recovery models), or have not had a Full backup in over 7 days, or have not had a Differential backup in over 1 day. If there has never been a backup, this is indicated in the results by returning “999” in the  “Days_Since_Last_Full,” “Days_Since_Last_TLog,” or “Days_Since_Last_Diff” columns. Calling with the “all_exceptions” parameter returns results sorted by recovery model, then database name.

In a follow-up post (click HERE) I will include a SQL Server Reporting Services report example which integrates nicely with this stored procedure. The “999” result is used in some of the formatting expressions in the SSRS report, so if you intend to use this report, you should not modify those parts of the procedure.

Here is the script. To easily copy the code, hover your mouse over the below section and click the “copy to clipboard” button in the tool bar that appears at the top right. If the copy button is not available, then click the “view source” button, press Ctrl + A to select all, and Ctrl + C to copy. Then paste into your favorite editor.

USE [master]
GO

CREATE PROC [dbo].[rpt_SQL_Backups_Report]

	@type VARCHAR(50) = 'all_exceptions'

AS

DECLARE @srvrName VARCHAR(500)
SELECT @srvrName = @@SERVERNAME

/*

	Created by Norm Enger 2/28/2011, based in part on a script at:

http://www.sqlservercentral.com/scripts/viewscript.asp?scriptid=715

	Shows status, recovery models, last full backup dates, last tlog backup dates,
	last diff backup dates, and days lapsed since each of those, for all dbs on a server.
	Works for SQL 2000 and above.

	The "all_alpha" sorts by db name.

	The "all_recovery_alpha" sorts by recovery model, then db name.

	The "all_exceptions" shows only ones that have TLog backups aged over 1 day,
	Full backups aged over 7 days, or Diff backups aged over 1 day, or any type
	that has never have had a backup (indicated by returning "999" in the
	"Days_Since_Last_Full, "Days_Since_Last_TLog", or "Days_Since_Last_Diff"
	results; and sorts by recovery model, then db name.

	Sample executions:
	EXEC rpt_SQL_Backups_Report 'all_exceptions'
	EXEC rpt_SQL_Backups_Report 'all_recovery_alpha'
	EXEC rpt_SQL_Backups_Report 'all_alpha'

*/

SET NOCOUNT ON

CREATE TABLE #TEMP
(
	DBName VARCHAR(500),
	[Recovery] VARCHAR(500),
	[DBStatus] VARCHAR(500)
)

DECLARE
	@dbname VARCHAR(500),
	@STR VARCHAR (8000)

DECLARE DBRECOVERYMODELGENERATOR_CURSOR CURSOR FOR
	SELECT name FROM sysdatabases
	WHERE category IN ('0', '1','16')
	ORDER BY name

OPEN DBRECOVERYMODELGENERATOR_CURSOR

FETCH NEXT FROM DBRECOVERYMODELGENERATOR_CURSOR INTO @dbname WHILE (@@FETCH_STATUS <> -1)

BEGIN
	IF (@@FETCH_STATUS <> -2)
	BEGIN
		SELECT @STR = 'SELECT ''' + @dbname + ''' AS DBName, CAST(DATABASEPROPERTYEX ('''+ @dbname + ''', ''recovery'') AS VARCHAR(500)) AS Recovery, CAST(DATABASEPROPERTYEX (''' + @dbname + ''', ''STATUS'') AS VARCHAR(500)) + CAST(CASE WHEN DATABASEPROPERTYEX(''' + @dbname + ''', ''IsInStandBy'') = 0 THEN '''' ELSE '' (Standby / Read-Only)'' END AS VARCHAR(500)) AS DBStatus'
		INSERT INTO #TEMP
		EXEC (@STR)
	END 

	FETCH NEXT FROM DBRECOVERYMODELGENERATOR_CURSOR INTO @dbname
END

CLOSE DBRECOVERYMODELGENERATOR_CURSOR

DEALLOCATE DBRECOVERYMODELGENERATOR_CURSOR

IF @type = 'all_alpha'
BEGIN
	SELECT
	a.[DBName],
	a.[Recovery],
	DBStatus =
	CASE
		WHEN a.[DBStatus] = 'ONLINE' THEN a.[DBStatus]
		ELSE '***' + a.[DBStatus] + '***'
	END,
	CAST(T.[Last_Full_Backup] AS varchar(50)) + ' to ' + T2.physical_device_name AS Last_Full_Backup,
	T.[Days_Since_Last_Full],
	CAST(U.[Last__TLog_Backup] AS varchar(50)) + ' to ' + U2.physical_device_name AS Last__TLog_Backup,
	U.[Days_Since_Last_TLog],
	CAST(V.[Last__Diff_Backup] AS varchar(50)) + ' to ' + V2.physical_device_name AS Last__Diff_Backup,
	V.[Days_Since_Last_Diff]

	FROM #TEMP a

	LEFT JOIN 

	-- Backup of data
	(SELECT database_name, MAX(backup_finish_date) AS backup_finish_date,
		CONVERT( SmallDateTime , MAX(backup_finish_date)) as Last_Full_Backup,
		DATEDIFF(d, MAX(backup_finish_date), Getdate()) as Days_Since_Last_Full
	FROM msdb.dbo.backupset WITH (NOLOCK)
	WHERE type = 'D'
	GROUP BY database_name
	--ORDER BY 3 DESC
	) T

	ON a.[DBName] = T.[database_name]

	LEFT JOIN

	--Get the device or filename the last Full backup was made to
	(SELECT bs.database_name, bs.backup_finish_date, mf.physical_device_name
	FROM msdb.dbo.backupset bs WITH (NOLOCK)

	INNER JOIN [msdb].[dbo].[backupmediafamily] mf WITH (NOLOCK)
	ON bs.media_set_id = mf.media_set_id
	) T2

	ON T.database_name = T2.database_name
	AND T.backup_finish_date = T2.backup_finish_date

	LEFT JOIN

	-- Find the backup of Transaction Log files
	(SELECT database_name, MAX(backup_finish_date) AS backup_finish_date,
		CONVERT( SmallDateTime , MAX(backup_finish_date)) as Last__TLog_Backup,
		DATEDIFF(d, MAX(backup_finish_date), Getdate()) as Days_Since_Last_TLog
	FROM msdb.dbo.backupset WITH (NOLOCK)
	WHERE type = 'L'
	GROUP BY database_name
	) U

	ON a.[DBName] = U.[database_name]

	LEFT JOIN

	--Get the device or filename the last TLog backup was made to
	(SELECT bs.database_name, bs.backup_finish_date, mf.physical_device_name
	FROM msdb.dbo.backupset bs WITH (NOLOCK)

	INNER JOIN [msdb].[dbo].[backupmediafamily] mf WITH (NOLOCK)
	ON bs.media_set_id = mf.media_set_id
	) U2

	ON U.database_name = U2.database_name
	AND U.backup_finish_date = U2.backup_finish_date

	LEFT JOIN

	-- Find the differential backups
	(SELECT database_name, MAX(backup_finish_date) AS backup_finish_date,
		CONVERT( SmallDateTime , MAX(backup_finish_date)) as Last__Diff_Backup,
		DATEDIFF(d, MAX(backup_finish_date), Getdate()) as Days_Since_Last_Diff
	FROM msdb.dbo.backupset WITH (NOLOCK)
	WHERE type = 'I'
	GROUP BY database_name
	) V

	ON a.[DBName] = V.[database_name]

	LEFT JOIN

	--Get the device or filename the last diff backup was made to
	(SELECT bs.database_name, bs.backup_finish_date, mf.physical_device_name
	FROM msdb.dbo.backupset bs WITH (NOLOCK)

	INNER JOIN [msdb].[dbo].[backupmediafamily] mf WITH (NOLOCK)
	ON bs.media_set_id = mf.media_set_id
	) V2

	ON V.database_name = V2.database_name
	AND V.backup_finish_date = V2.backup_finish_date

	ORDER BY DBName
END

IF @type = 'all_recovery_alpha'
BEGIN
	SELECT
	ServerName = @srvrName,
	a.[DBName],
	a.[Recovery],
	DBStatus =
	CASE
		WHEN a.[DBStatus] = 'ONLINE' THEN a.[DBStatus]
		ELSE '***' + a.[DBStatus] + '***'
	END,
	CAST(T.[Last_Full_Backup] AS varchar(50)) + ' to ' + T2.physical_device_name AS Last_Full_Backup,
	ISNULL(T.[Days_Since_Last_Full],999) AS [Days_Since_Last_Full],
	CAST(U.[Last__TLog_Backup] AS varchar(50)) + ' to ' + U2.physical_device_name AS Last__TLog_Backup,
	ISNULL(U.[Days_Since_Last_TLog],999) AS [Days_Since_Last_TLog],
	CAST(V.[Last__Diff_Backup] AS varchar(50)) + ' to ' + V2.physical_device_name AS Last__Diff_Backup,
	ISNULL(V.[Days_Since_Last_Diff],999) AS [Days_Since_Last_Diff]

	FROM #TEMP a

	LEFT JOIN 

	-- Backup of data
	(SELECT database_name, MAX(backup_finish_date) AS backup_finish_date,
		CONVERT( SmallDateTime , MAX(backup_finish_date)) as Last_Full_Backup,
		DATEDIFF(d, MAX(backup_finish_date), Getdate()) as Days_Since_Last_Full
	FROM msdb.dbo.backupset WITH (NOLOCK)
	WHERE type = 'D'
	GROUP BY database_name
	--ORDER BY 3 DESC
	) T

	ON a.[DBName] = T.[database_name]

	LEFT JOIN

	--Get the device or filename the last Full backup was made to
	(SELECT bs.database_name, bs.backup_finish_date, mf.physical_device_name
	FROM msdb.dbo.backupset bs WITH (NOLOCK)

	INNER JOIN [msdb].[dbo].[backupmediafamily] mf WITH (NOLOCK)
	ON bs.media_set_id = mf.media_set_id
	) T2

	ON T.database_name = T2.database_name
	AND T.backup_finish_date = T2.backup_finish_date

	LEFT JOIN

	-- Find the backup of Transaction Log files
	(SELECT database_name, MAX(backup_finish_date) AS backup_finish_date,
		CONVERT( SmallDateTime , MAX(backup_finish_date)) as Last__TLog_Backup,
		DATEDIFF(d, MAX(backup_finish_date), Getdate()) as Days_Since_Last_TLog
	FROM msdb.dbo.backupset WITH (NOLOCK)
	WHERE type = 'L'
	GROUP BY database_name
	) U

	ON a.[DBName] = U.[database_name]

	LEFT JOIN

	--Get the device or filename the last TLog backup was made to
	(SELECT bs.database_name, bs.backup_finish_date, mf.physical_device_name
	FROM msdb.dbo.backupset bs WITH (NOLOCK)

	INNER JOIN [msdb].[dbo].[backupmediafamily] mf WITH (NOLOCK)
	ON bs.media_set_id = mf.media_set_id
	) U2

	ON U.database_name = U2.database_name
	AND U.backup_finish_date = U2.backup_finish_date

	LEFT JOIN

	-- Find the differential backups
	(SELECT database_name, MAX(backup_finish_date) AS backup_finish_date,
		CONVERT( SmallDateTime , MAX(backup_finish_date)) as Last__Diff_Backup,
		DATEDIFF(d, MAX(backup_finish_date), Getdate()) as Days_Since_Last_Diff
	FROM msdb.dbo.backupset WITH (NOLOCK)
	WHERE type = 'I'
	GROUP BY database_name
	) V

	ON a.[DBName] = V.[database_name]

	LEFT JOIN

	--Get the device or filename the last diff backup was made to
	(SELECT bs.database_name, bs.backup_finish_date, mf.physical_device_name
	FROM msdb.dbo.backupset bs WITH (NOLOCK)

	INNER JOIN [msdb].[dbo].[backupmediafamily] mf WITH (NOLOCK)
	ON bs.media_set_id = mf.media_set_id
	) V2

	ON V.database_name = V2.database_name
	AND V.backup_finish_date = V2.backup_finish_date

	ORDER BY [Recovery], DBName
END

IF @type = 'all_exceptions'
BEGIN
	SELECT
	ServerName = @srvrName,
	a.[DBName],
	a.[Recovery],
	DBStatus =
	CASE
		WHEN a.[DBStatus] = 'ONLINE' THEN a.[DBStatus]
		ELSE '***' + a.[DBStatus] + '***'
	END,
	CAST(T.[Last_Full_Backup] AS varchar(50)) + ' to ' + T2.physical_device_name AS Last_Full_Backup,
	ISNULL(T.[Days_Since_Last_Full],999) AS [Days_Since_Last_Full],
	CAST(U.[Last__TLog_Backup] AS varchar(50)) + ' to ' + U2.physical_device_name AS Last__TLog_Backup,
	ISNULL(U.[Days_Since_Last_TLog],999) AS [Days_Since_Last_TLog],
	CAST(V.[Last__Diff_Backup] AS varchar(50)) + ' to ' + V2.physical_device_name AS Last__Diff_Backup,
	ISNULL(V.[Days_Since_Last_Diff],999) AS [Days_Since_Last_Diff]

	FROM #TEMP a

	LEFT JOIN 

	-- Backup of data
	(SELECT database_name, MAX(backup_finish_date) AS backup_finish_date,
		CONVERT( SmallDateTime , MAX(backup_finish_date)) as Last_Full_Backup,
		DATEDIFF(d, MAX(backup_finish_date), Getdate()) as Days_Since_Last_Full
	FROM msdb.dbo.backupset WITH (NOLOCK)
	WHERE type = 'D'
	GROUP BY database_name
	--ORDER BY 3 DESC
	) T

	ON a.[DBName] = T.[database_name]

	LEFT JOIN

	--Get the device or filename the last Full backup was made to
	(SELECT bs.database_name, bs.backup_finish_date, mf.physical_device_name
	FROM msdb.dbo.backupset bs WITH (NOLOCK)

	INNER JOIN [msdb].[dbo].[backupmediafamily] mf WITH (NOLOCK)
	ON bs.media_set_id = mf.media_set_id
	) T2

	ON T.database_name = T2.database_name
	AND T.backup_finish_date = T2.backup_finish_date

	LEFT JOIN

	-- Find the backup of Transaction Log files
	(SELECT database_name, MAX(backup_finish_date) AS backup_finish_date,
		CONVERT( SmallDateTime , MAX(backup_finish_date)) as Last__TLog_Backup,
		DATEDIFF(d, MAX(backup_finish_date), Getdate()) as Days_Since_Last_TLog
	FROM msdb.dbo.backupset WITH (NOLOCK)
	WHERE type = 'L'
	GROUP BY database_name
	) U

	ON a.[DBName] = U.[database_name]

	LEFT JOIN

	--Get the device or filename the last TLog backup was made to
	(SELECT bs.database_name, bs.backup_finish_date, mf.physical_device_name
	FROM msdb.dbo.backupset bs WITH (NOLOCK)

	INNER JOIN [msdb].[dbo].[backupmediafamily] mf WITH (NOLOCK)
	ON bs.media_set_id = mf.media_set_id
	) U2

	ON U.database_name = U2.database_name
	AND U.backup_finish_date = U2.backup_finish_date

	LEFT JOIN

	-- Find the differential backups
	(SELECT database_name, MAX(backup_finish_date) AS backup_finish_date,
		CONVERT( SmallDateTime , MAX(backup_finish_date)) as Last__Diff_Backup,
		DATEDIFF(d, MAX(backup_finish_date), Getdate()) as Days_Since_Last_Diff
	FROM msdb.dbo.backupset WITH (NOLOCK)
	WHERE type = 'I'
	GROUP BY database_name
	) V

	ON a.[DBName] = V.[database_name]

	LEFT JOIN

	--Get the device or filename the last diff backup was made to
	(SELECT bs.database_name, bs.backup_finish_date, mf.physical_device_name
	FROM msdb.dbo.backupset bs WITH (NOLOCK)

	INNER JOIN [msdb].[dbo].[backupmediafamily] mf WITH (NOLOCK)
	ON bs.media_set_id = mf.media_set_id
	) V2

	ON V.database_name = V2.database_name
	AND V.backup_finish_date = V2.backup_finish_date

	WHERE
		(
			(
			T.[Days_Since_Last_Full] > 7
			)
			OR
			(
			U.[Days_Since_Last_TLog] > 1
			AND a.[Recovery] IN ('FULL','BULK_LOGGED')
			)
			OR
			(
			T.[Days_Since_Last_Full] IS NULL
			)
			OR
			(
			U.[Days_Since_Last_TLog] IS NULL
			AND a.[Recovery] IN ('FULL','BULK_LOGGED')
			)
			OR
			(
			a.[DBName] NOT IN ('tempdb', 'master', 'model', 'msdb', 'pubs', 'northwind')
			AND
			a.[DBName] NOT LIKE 'adventurework%'
			AND
			V.[Days_Since_Last_Diff] > 1
			)
			OR
			(
			a.[DBName] NOT IN ('tempdb', 'master', 'model', 'msdb', 'pubs', 'northwind')
			AND
			a.[DBName] NOT LIKE 'adventurework%'
			AND
			V.[Days_Since_Last_Diff] IS NULL
			)
		)
		AND
		a.[DBName] NOT IN ('tempdb', 'model', 'pubs', 'northwind')
		AND
		a.[DBName] NOT LIKE 'adventurework%'

	ORDER BY [Recovery], DBName
END

DROP TABLE #TEMP

Posted February 28, 2011 by Norm Enger in Microsoft SQL Server

Posted February 28, 2011 by Norm Enger in Microsoft SQL Server

The Tragedy in Tucson   Leave a comment

I am privileged to live in the city of Tucson. I posted a blog entitled “The Tragedy in Tucson” over on my personal Web site. Click HERE to read this blog.

Posted January 29, 2011 by Norm Enger in Personal

Detect When Your tempdb Files Have Grown   Leave a comment

One goal of managing your tempdb database files should be to size them appropriately in order to avoid auto-growth. Here is a handy script that works on all versions of SQL Server (2000 and above) to show whether your tempdb data and log files have grown beyond their initial sizes (the sizes they will be upon restarting of the SQL Server services). In other words, the script will tell you if auto-growth has occurred.

In most cases, if auto-growth has occurred, you will want to increase the initial size of an individual file so that this will not occur. Changing the initial size can be done easily through the SSMS GUI by expanding Databases –> System Databases and right-clicking on tempdb –> Properties. Then in the Properties window, go to the Files tab and adjust the size.

The script displays the initial size for each tempdb file, the current file size for each file, and growth beyond the initial size that has occurred, in MB, if any. As well, it displays the logical and physical file names and paths, current used and free space within the files, and auto-growth settings (e.g., “Autogrowth: By 10 percent, unrestricted growth”).

A suggested way to use this script, which I have implemented but have not demonstrated here, is to schedule it to run against each of your SQL instances daily, and if auto-growth has occurred, notify you via email.

Here is the script. To easily copy the code, hover your mouse over the below section and click the “copy to clipboard” button in the tool bar that appears at the top right. If the copy button is not available, then click the “view source” button, press Ctrl + A to select all, and Ctrl + C to copy. Then paste into your favorite editor.

--tempdb - Show files info with initial tempdb size integrated

USE [master]

IF OBJECT_ID('tempdb..#data') IS NULL
	CREATE TABLE #data(
	dbname VARCHAR(300),
	dbcompat VARCHAR(10),
	Fileid int NOT NULL,
	[FileGroup] int NOT NULL,
	TotalExtents int NOT NULL,
	UsedExtents int NOT NULL,
	[Name] sysname NOT NULL,
	[FileName] varchar(300) NOT NULL,
	FilePath VARCHAR(500) NULL,
	Autogrowth	 VARCHAR(500) NULL
)

IF OBJECT_ID('tempdb..#log') IS NULL
CREATE TABLE #log(
	id int identity(1,1) not null,
	groupid INT DEFAULT 0 NULL,
	dbname sysname NOT NULL,
	dbcompat VARCHAR(10),
	LogSize numeric(38,7) NOT NULL,
	LogUsed numeric(38,6) NOT NULL,
	Status int NOT NULL,
	[Name] VARCHAR(300) NULL,
	FilePath VARCHAR(500) NULL,
	Autogrowth	 VARCHAR(500) NULL
)

USE [tempdb] 

INSERT #data (Fileid, FileGroup, TotalExtents, UsedExtents, Name, FileName)
EXEC('DBCC showfilestats with no_infomsgs') 

UPDATE #data SET dbname = 'tempdb'
WHERE dbname IS NULL 

UPDATE #data
SET [FilePath] = s.filename,
Autogrowth = 'Autogrowth: '
+
CASE
	WHEN (s.status & 0x100000 = 0 AND CEILING((s.growth * 8192.0)/(1024.0*1024.0)) = 0.00)
		OR s.growth = 0 THEN 'None'
	WHEN s.status & 0x100000 = 0 THEN 'By ' + CONVERT(VARCHAR,CEILING((s.growth * 8192.0)/(1024.0*1024.0))) + ' MB'
	ELSE 'By ' + CONVERT(VARCHAR, s.growth) + ' percent' END
+
CASE
	WHEN (s.status & 0x100000 = 0 AND CEILING((s.growth * 8192.0)/(1024.0*1024.0)) = 0.00)
		OR s.growth = 0 THEN ''
	WHEN CAST([maxsize]*8.0/1024 AS DEC(20,2)) <= 0.00 THEN ', unrestricted growth'
	ELSE ', restricted growth to ' + CAST(CAST([maxsize]*8.0/1024 AS DEC(20)) AS VARCHAR) + ' MB'
END
FROM #data d
INNER JOIN dbo.sysfiles s
ON d.FileGroup = s.groupid AND d.Fileid = s.fileid
WHERE d.dbname = 'tempdb'

USE [tempdb];
INSERT #log
(
	dbname,
	LogSize,
	LogUsed,
	Status
)
EXEC('DBCC sqlperf(logspace) with no_infomsgs');
INSERT #log
(
	dbname,
	[groupid],
	LogSize,
	LogUsed,
	Status,
	[Name]
)
SELECT 
	'tempdb',
	[s].[groupid],
	s.[size] * 0.0078125 AS [size],
	0,
	0,
	[name]
FROM dbo.sysfiles s
WHERE [groupid] = 0;

USE [tempdb];

UPDATE #log
SET [#log].[LogUsed] = b.[LogUsed]
FROM [#log] t
INNER JOIN
 ( SELECT 
		[l].[dbname],
		[LogUsed]
	FROM #log l
	LEFT JOIN
		( SELECT dbname,
		Name,
		MIN(id) AS id
		FROM #log
		GROUP BY dbname,
		Name
		) k
	ON l.dbname = k.dbname
	AND l.Name = k.Name
	AND l.id = k.id
	WHERE k.id IS NULL
	)
	AS b
	ON [t].[dbname] = [b].[dbname];

DELETE #log
WHERE Name IS NULL;

USE [master];
UPDATE #log
SET [Name] = s.name,
[FilePath] = s.filename,
Autogrowth = 'Autogrowth: ' +
CASE
	WHEN
	(
	s.status & 0x100000 = 0
	AND CEILING((s.growth * 8192.0)/(1024.0*1024.0)) = 0.00
	)
	OR s.growth = 0
	THEN 'None'
	WHEN s.status & 0x100000 = 0
	THEN 'By ' + CONVERT(VARCHAR,CEILING((s.growth * 8192.0)/(1024.0*1024.0))) + ' MB'
	ELSE 'By ' + CONVERT(VARCHAR, s.growth) + ' percent'
END +
CASE
	WHEN
	(
	s.status & 0x100000 = 0
	AND CEILING((s.growth * 8192.0)/(1024.0*1024.0)) = 0.00
	)
	OR s.growth = 0
	THEN ''
	WHEN CAST([maxsize]*8.0/1024 AS DEC(38,7)) <= 0.00
	THEN ', unrestricted growth'
	ELSE ', restricted growth to ' + CAST(CAST([maxsize]*8.0/1024 AS DEC(38,7)) AS VARCHAR) + ' MB'
END
FROM #log l
INNER JOIN [tempdb].dbo.sysfiles s
	ON l.groupid = s.groupid
	AND l.[Name] COLLATE DATABASE_DEFAULT = s.[name] COLLATE DATABASE_DEFAULT
WHERE l.dbname = 'tempdb';

--SELECT * FROM [#data]
--SELECT * FROM [#log] WHERE dbname = 'tempdb'

SELECT
DB_Name,
[Type],
[Name],
[FilePath],
init_size_MB,
[CurrTotalMB],
tempdb_growth_MB =
CASE
	WHEN [CurrTotalMB] - init_size_MB < 0 THEN 0
	ELSE [CurrTotalMB] - init_size_MB
END,
UsedMB,
FreeMB,
Autogrowth
FROM
(
SELECT
	dbname AS [DB_Name],
	'Data File' AS [Type],
	[Name] AS [Name],
	[FilePath],
	init_size_MB = a.Initial_Size_MB,
	(TotalExtents*64)/1024.0 AS [CurrTotalMB],
	(UsedExtents*64)/1024.0 AS [UsedMB],
	(TotalExtents*64)/1024.0 - (UsedExtents*64)/1024.0 AS [FreeMB],
	[Autogrowth]
FROM #data d
	INNER JOIN
	(
		SELECT
			[DB_Name] = 'tempdb',
			f.[name] AS logical_file_Name,
			(CAST(f.[size] AS DECIMAL) * 8192) / 1024 /1024 AS Initial_Size_MB
		FROM master..sysaltfiles f
			INNER JOIN master..sysdatabases d
			ON f.dbid = d.dbid
		WHERE d.name = 'tempdb') AS a
	ON d.dbname = a.[DB_Name]
	AND d.[Name] = a.logical_file_Name
WHERE [dbname] = 'tempdb'
UNION
SELECT
	dbname AS [DB_Name],
	'TLog File',
	[Name] AS [NAME],
	[FilePath],
	init_size_MB = a.Initial_Size_MB,
	LogSize,
	((LogUsed/100)*LogSize),
	LogSize - ((LogUsed/100)*LogSize),
	[Autogrowth]
FROM #log l
	INNER JOIN
	(
		SELECT
			[DB_Name] = 'tempdb',
			f.[name] AS logical_file_Name,
			(CAST(f.[size] AS DECIMAL) * 8192) / 1024 /1024 AS Initial_Size_MB
		FROM master..sysaltfiles f
			INNER JOIN master..sysdatabases d
			ON f.dbid = d.dbid
		WHERE d.name = 'tempdb') AS a
	ON l.dbname = a.[DB_Name]
	AND l.[Name] = a.logical_file_Name
WHERE [dbname] = 'tempdb'
) AS r
ORDER BY 1, [Type], [Name]

IF OBJECT_ID('tempdb..#data') IS NOT NULL
DROP TABLE [#data]
IF OBJECT_ID('tempdb..#log') IS NOT NULL
DROP TABLE [#log]

Posted January 1, 2011 by Norm Enger in Microsoft SQL Server

Posted January 1, 2011 by Norm Enger in Microsoft SQL Server

Applying SP2 or SP3 to a Rebuilt SQL 2005 Cluster Node   Leave a comment

Recently I ran into an issue with rebuilding the nodes of a two-node MSCS SQL 2005 64 bit cluster. We were rebuilding one node at a time in order to convert the machines to be boot-from-SAN. This is so that the system drives could participate in SAN-level replication taking place to an off-site DR location. The SQL instances were patched to SP3 plus a post-SP3 patch level.

These are running on Windows 2003 Ent. Ed. SP2. As an interesting side note, to enable Windows 2003 clustering to be able to manage the SAN disks as clustered or shared disks, you will need to add a registry entry, per the article, “How to add a registry value to a Windows Server 2003-based computer that you start from a SAN so that the startup disk, the pagefile disks, and the cluster disks are all on the same SAN fabric.”

Back to the topic at hand. The issue we ran into was that after rebuilding a node, and after installing the RTM version of SQL 2005 to the rebuilt node (let’s say Node “A” in this case), we could not get the SP3 installer to stop trying to apply the patching to the existing (already-patched) node (let’s say Node “B”). What would happen is that the installer would try to update the passive node (“B”) first, and would fail and quit the process every time, before updating the rebuilt node.

It turns out the best solution for this is to temporarily set the “Possible Owners” settings for each individual resource in your SQL cluster groups, using Cluster Administrator, to be, in our example, the “A” node, i.e., temporarily remove the other nodes (“B” in our example) as possible owners. Once these were set, we were able to successfully apply the SP3 update and post-SP3 updates needed, in order to bring the rebuilt node (“A”) to the same build level as the existing node.

I am not entirely sure why this was necessary, but it seems the installer program decides which nodes to run the patching against based on the “Possible Owners” settings for the SQL groups’ resources.

Afterwards when patching is complete, be sure to set the “Possible Owners” settings back to the way they were. After this you will have both machines properly patched to the same build level, as they should be. You would apply this same technique later when rebuilding the “B” Node.

Posted December 12, 2010 by Norm Enger in Microsoft SQL Server

SQL Server 2000/2005 Security Audit Scripts   1 comment

Originally published Saturday, June 30, 2007 at http://www.biblereadingschedule.com/nucleus?itemid=62&catid=6

Here is a series of discreet scripts designed to assist in the security audit of a SQL 2000 or 2005 instance. I have never seen scripts of this type gathered together in one place. I have made modifications where applicable to allow these scripts to audit all of the databases in a SQL instance in one run, making all of these suitable for a SQL instance security report, using tools such as Reporting Services.

Here is the script. To easily copy the code, hover your mouse over the below section and click the “copy to clipboard” button in the tool bar that appears at the top right. If the copy button is not available, then click the “view source” button, press Ctrl + A to select all, and Ctrl + C to copy. Then paste into your favorite editor.

/*
	Norm Enger, 6/2007. The following is a series of discreet scripts designed
	to assist in the security audit of a SQL 2000 or 2005 instance. Many of the ideas
	for the scripts and some of the code came from FullSun SQLServerToolKit
	(http://www2.fullsun.com:8000/Software/SQLServerToolKit/SQLServerToolKit.htm) or
	other sources, and I have given credit where possible.

	I have never seen scripts of this type gathered together in one place, which
	seems odd since security is such an important aspect of SQL Server. I have made
	modifications where applicable to allow these scripts to audit all of the
	databases in a SQL instance in one run, making all of them suitable for a
	SQL instance security report, using tools such as Reporting Services.

	Happy auditing!
*/

--Detect server wide roles assigned to users - sysadmins and other roles - works with both SQL 2000 and 2005

USE master

--Sysadmins

SELECT
	name AS Login,
	sysadmin =
	CASE
		WHEN sysadmin = 1 THEN 'X'
		ELSE ''
	END,
	securityadmin =
	CASE
		WHEN securityadmin = 1 THEN 'X'
		ELSE ''
	END,
	serveradmin =
	CASE
		WHEN serveradmin = 1 THEN 'X'
		ELSE ''
	END,
	setupadmin =
	CASE
		WHEN setupadmin = 1 THEN 'X'
		ELSE ''
	END,
	processadmin =
	CASE
		WHEN processadmin = 1 THEN 'X'
		ELSE ''
	END,
	diskadmin =
	CASE
		WHEN diskadmin = 1 THEN 'X'
		ELSE ''
	END,
	dbcreator =
	CASE
		WHEN dbcreator = 1 THEN 'X'
		ELSE ''
	END,
	bulkadmin =
	CASE
		WHEN bulkadmin = 1 THEN 'X'
		ELSE ''
	END,
	CONVERT(CHAR(16),createdate,120) AS 'DateCreated'
FROM master.dbo.syslogins
WHERE
	sysadmin = 1
ORDER BY NAME

GO

--Any other server-wide role, not including ones in the first list above. Works with both SQL 2000 and 2005

SELECT
	name AS Login,
	securityadmin =
	CASE
		WHEN securityadmin = 1 THEN 'X'
		ELSE ''
	END,
	serveradmin =
	CASE
		WHEN serveradmin = 1 THEN 'X'
		ELSE ''
	END,
	setupadmin =
	CASE
		WHEN setupadmin = 1 THEN 'X'
		ELSE ''
	END,
	processadmin =
	CASE
		WHEN processadmin = 1 THEN 'X'
		ELSE ''
	END,
	diskadmin =
	CASE
		WHEN diskadmin = 1 THEN 'X'
		ELSE ''
	END,
	dbcreator =
	CASE
		WHEN dbcreator = 1 THEN 'X'
		ELSE ''
	END,
	bulkadmin =
	CASE
		WHEN bulkadmin = 1 THEN 'X'
		ELSE ''
	END,
	CONVERT(CHAR(16),createdate,120) AS 'DateCreated'
FROM master.dbo.syslogins
WHERE
	(securityadmin = 1
	OR serveradmin = 1
	OR setupadmin = 1
	OR processadmin = 1
	OR diskadmin = 1
	OR dbcreator = 1
	OR bulkadmin = 1)
	AND sysadmin <> 1
ORDER BY NAME

USE master
GO

--Blank or easily guessed passwords. Works with both SQL 2000 and 2005.
IF OBJECT_ID('dbo.spAuditPasswords') IS NOT NULL
	DROP PROCEDURE dbo.spAuditPasswords
GO

CREATE PROCEDURE dbo.spAuditPasswords

AS 

/* 

	Modified by Norm Enger 6/14/2007. Works with both SQL 2000 and 2005.

	Original script obtained courtesy of Randy Dyess, from
	http://www.fits-consulting.de/blog/PermaLink,guid,4f42c907-3e39-4283-a4fb-c3e536ce2ceb.aspx

Creation Date: 03/22/02 Created By: Randy Dyess
Web Site: www.TransactSQL.Com
Email: RandyDyess@TransactSQL.Com
Purpose: Perform a simple audit of user's passwords
Location: master database
Output Parameters: None
Return Status: None
Called By: None
Calls: None
Data Modifications: None
Updates: None
Date Author Purpose 

*/ 

SET NOCOUNT ON 

--Variables
DECLARE @lngCounter INTEGER
DECLARE @lngCounter1 INTEGER
DECLARE @lngLogCount INTEGER
DECLARE @strName VARCHAR(256)

--Create table to hold SQL logins
CREATE TABLE #tLogins
(
numID INTEGER IDENTITY(1,1)
,strLogin sysname NULL
,lngPass integer NULL
,Password varchar(500) NULL
,Type int NULL
)

--Insert non ntuser into temp table
INSERT INTO #tLogins (strLogin)
SELECT name FROM master.dbo.syslogins WHERE isntname = 0
SET @lngLogCount = @@ROWCOUNT 

--Determine if password and name are the ssame
SET @lngCounter = @lngLogCount

WHILE @lngCounter <> 0
BEGIN
    SET @strName = (SELECT strLogin FROM #tLogins WHERE numID = @lngCounter)

    UPDATE #tLogins
    SET
		lngPass = (SELECT PWDCOMPARE (@strName,(SELECT password FROM master.dbo.syslogins WHERE name = @strName))),
		Type =
		CASE
			WHEN (SELECT PWDCOMPARE (@strName,(SELECT password FROM master.dbo.syslogins WHERE name = @strName))) = 1 THEN 2 -- Password same as login
			ELSE NULL
		END
    WHERE numID = @lngCounter
    AND Type IS NULL

    SET @lngCounter = @lngCounter - 1
END 

--Reset column for next password test
UPDATE #tLogins
SET lngPass = 0

--Determine if password is only one character long
SET @lngCounter = @lngLogCount

WHILE @lngCounter <> 0
BEGIN
    SET @lngCounter1 = 1
    SET @strName = (SELECT strLogin FROM #tLogins WHERE numID = @lngCounter)
    WHILE @lngCounter1 < 256
    BEGIN
        UPDATE #tLogins
        SET lngPass = (SELECT PWDCOMPARE (CHAR(@lngCounter1),(SELECT password FROM master.dbo.syslogins WHERE name = @strName))), Password = UPPER(CHAR(@lngCounter1)) + ' or ' + LOWER(CHAR(@lngCounter1)),
		Type =
		CASE
			WHEN (SELECT PWDCOMPARE (CHAR(@lngCounter1),(SELECT password FROM master.dbo.syslogins WHERE name = @strName))) = 1 THEN 3 --password is only one character long
			ELSE NULL
		END
        WHERE numID = @lngCounter
        AND lngPass <> 1
        AND Type IS NULL

        SET @lngCounter1 = @lngCounter1 + 1

    END 

    SET @lngCounter = @lngCounter - 1
END 

--Return combined results
SELECT name AS 'Login Name', Passsword = '(BLANK)' FROM master.dbo.syslogins
WHERE password IS NULL
AND isntname = 0
	UNION ALL
SELECT strLogin AS 'Login Name', Password = strLogin FROM #tLogins WHERE Type = 2
	UNION ALL
SELECT 'Login Name' = strLogin, Password FROM #tLogins WHERE Type = 3
ORDER BY name

GO

--Detect disabled or deleted Windows logins; works with both SQL 2000 and 2005

USE master

EXEC sp_validatelogins

GO

--Detect logins created in last 30 days
SELECT
	name AS 'Login Name',
	CONVERT(CHAR(16),createdate,120) AS 'Date Created'
FROM master.dbo.syslogins
WHERE createdate >= DATEADD(dd,-30,GETDATE())
ORDER BY createdate DESC

GO

--Detect orphan users in all dbs on a SQL instance; works in both SQL 2000 and 2005.
USE master
GO

IF OBJECT_ID('rpt_security_detect_db_orphans') IS NOT NULL
	DROP PROCEDURE [dbo].[rpt_security_detect_db_orphans]
GO

CREATE PROCEDURE dbo.rpt_security_detect_db_orphans

AS

/*

	NE 6/11/2007 - Detect orphan users in all dbs on a SQL instance; works in both SQL 2000 and 2005.

	EXEC rpt_security_detect_db_orphans

*/

DECLARE
	@dbname varchar(200),
	@sql varchar(8000)

DECLARE @temp table
(
	dbname VARCHAR(500)
)

CREATE TABLE #dbOrphanUsers
(
	DbName varchar(500),
	[User] varchar(500)
)

INSERT INTO @temp
	SELECT name
	FROM sysdatabases
	WHERE
		category IN ('0', '1','16')
		AND
		name NOT IN ('tempdb', 'model', 'pubs', 'northwind')
		AND
		name NOT LIKE 'adventurework%'
		AND DATABASEPROPERTYEX(name, 'status') = 'ONLINE'
	ORDER BY name

SELECT @dbname = MIN(dbname) FROM @temp

WHILE @dbname IS NOT NULL
BEGIN

	SET @sql =
	'INSERT INTO #dbOrphanUsers
	(DbName, [User])
	SELECT DbName = ''' + @dbname + ''', name AS [User]
	FROM [' + @dbname + '].dbo.sysusers
	WHERE
		issqluser = 1
		and (sid is not null
		and sid <> 0x0)
		and suser_sname(sid) is null'

	EXEC(@sql)	

	SELECT @dbname = MIN(dbname) FROM @temp WHERE dbname > @dbname

END

SELECT * FROM #dbOrphanUsers ORDER BY DbName, [User]

DROP TABLE #dbOrphanUsers

GO

-- Detect User permissions for all databases and users - SQL 2000 version only
-- Also incuded below is a modified version of sp_helprotect (named as sp_helprotect2) to be called by this.

USE master
GO

IF OBJECT_ID('rpt_security_detect_user_permissions_2000') IS NOT NULL
	DROP PROCEDURE [dbo].[rpt_security_detect_user_permissions_2000]
GO

CREATE PROCEDURE dbo.rpt_security_detect_user_permissions_2000

AS

/*

	NE 6/11/2007 - Detect User permissions for all databases and users - SQL 2000 version only.

	EXEC rpt_security_detect_user_permissions_2000

*/

SET NOCOUNT ON

CREATE TABLE #Permissions (dbname varchar(500), UserName varchar(500), owner varchar(500),object varchar(500),Grantee varchar(500),Grantor varchar(500),ProtectType varchar(500),Act varchar(500),Col varchar(50))

CREATE TABLE #Roles (dbname varchar(500), UserName varchar(500),GroupName varchar(500),LoginName varchar(500),DefDBName varchar(500),UserID varchar(500),SID varchar(50))

DECLARE
	@dbname varchar(200),
	@sql varchar(8000)

DECLARE @temp table
(
	dbname VARCHAR(500)
)

INSERT INTO @temp
	SELECT name
	FROM master.dbo.sysdatabases WITH (NOLOCK)
	WHERE
		category IN ('0', '1','16')
		AND
		name NOT IN ('tempdb', 'model', 'pubs', 'northwind')
		AND
		name NOT LIKE 'adventurework%'
		AND DATABASEPROPERTYEX(name, 'status') = 'ONLINE'
	ORDER BY name

SELECT @dbname = MIN(dbname) FROM @temp

WHILE @dbname IS NOT NULL
BEGIN

	SET @sql =
	'use [' + @dbname + '];
	declare @users table([User] varchar(500));
	declare @currUser varchar(500);
	insert into @users

	SELECT [name]
	FROM [' + @dbname + '].dbo.sysusers WITH (NOLOCK)
	WHERE hasdbaccess = 1 AND [name] <> ''dbo''

	SELECT @currUser = min([User]) from @users;
	WHILE @currUser IS NOT NULL
	BEGIN
		insert into #Permissions
		(owner, object, Grantee, Grantor, ProtectType, Act ,Col)
		exec sp_helprotect2 @username = @currUser
		insert into #Roles
		(UserName, GroupName, LoginName, DefDBName, UserID, SID)
		exec sp_helpuser @name_in_db = @currUser

		update #Permissions set dbname = ''' + @dbname + ''', UserName = @currUser where dbname is null
		update #Roles set dbname = ''' + @dbname + ''' where dbname is null

		select @currUser = min([User]) from @users where [User] > @currUser
	END'

	EXEC(@sql)	

	SELECT @dbname = MIN(dbname) FROM @temp WHERE dbname > @dbname
END

SELECT
	dbname AS DB_Name,
	UserName,
	'Permissions' = 'SP_AddRoleMember ''' + RTRIM(GroupName)+''', '''+ RTRIM(UserName)+''''
FROM #Roles
UNION
SELECT
	dbname AS DB_Name,
	UserName,
	UPPER(RTRIM(ProtectType))+' '+UPPER(RTRIM(Act))+' ON ['+owner+'].['+object+']'+
	CASE
		WHEN (PATINDEX('%All%', Col)=0) AND (Col <> '.') THEN ' ('+Col+')'
		ELSE ''
	END
	 + ' TO ['+Grantee+']'
FROM #Permissions
ORDER BY dbname, UserName, Permissions DESC

drop table #Permissions
drop table #Roles

GO

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

--Modified version of sp_helprotect (named as sp_helprotect2) - must be used with the SQL 2000 only version above...

USE master
GO

IF OBJECT_ID('sp_helprotect2') IS NOT NULL
	DROP PROCEDURE dbo.sp_helprotect2
GO

CREATE PROCEDURE dbo.sp_helprotect2
	@name				ncharacter varying(776)  = NULL
	,@username			sysname  = NULL
	,@grantorname		sysname  = NULL
	,@permissionarea	character varying(10)  = 'o s'
as

/********
Explanation of the parms...
---------------------------
@name:  Name of [Owner.]Object and Statement; meaning
for sysprotects.id and sysprotects.action at the
same time; thus see parm @permissionarea.
   Examples-   'user2.tb'  , 'CREATE TABLE', null

@username:  Name of the grantee (for sysprotects.uid).
   Examples-   'user2', null

@grantorname:  Name of the grantor (for sysprotects.grantor).
   Examples-   'user2' --Would prevent report rows which would
                       --  have 'dbo' as grantor.

@permissionarea:  O=Object, S=Statement; include all which apply.
   Examples-   'o'  , ',s'  , 'os'  , 'so'  , 's o'  , 's,o'
GeneMi
********/

	Set nocount on

	Declare
	@vc1                   sysname
	,@Int1                  integer

	Declare
	@charMaxLenOwner		character varying(11)
	,@charMaxLenObject		character varying(11)
	,@charMaxLenGrantee		character varying(11)
	,@charMaxLenGrantor		character varying(11)
	,@charMaxLenAction		character varying(11)
	,@charMaxLenColumnName	character varying(11)

	Declare
	@OwnerName				sysname
	,@ObjectStatementName	sysname

	/* Perform temp table DDL here to minimize compilation costs*/
CREATE Table #t1_Prots
	(	Id					int				Null
		,Type1Code			char(6)			collate database_default NOT Null
		,ObjType			char(2)			collate database_default Null

		,ActionName		varchar(20)			collate database_default Null
		,ActionCategory	char(2)				collate database_default Null
		,ProtectTypeName	char(10)		collate database_default Null

		,Columns_Orig		varbinary(32)	Null

		,OwnerName			sysname			collate database_default NOT Null
		,ObjectName			sysname			collate database_default NOT Null
		,GranteeName		sysname			collate database_default NOT Null
		,GrantorName		sysname			collate database_default NOT Null

		,ColumnName			sysname			collate database_default Null
		,ColId				smallint		Null

		,Max_ColId			smallint		Null
		,All_Col_Bits_On	tinyint			Null
		,new_Bit_On			tinyint			Null )  -- 1=yes on

	/*	Check for valid @permissionarea */
	Select @permissionarea = upper( isnull(@permissionarea,'?') )

	IF (	charindex('O',@permissionarea) <= 0
		AND  charindex('S',@permissionarea) <= 0)
	begin
		raiserror(15300,-1,-1 ,@permissionarea,'o,s')
		return (1)
	end

	select @vc1 = parsename(@name,3)

	/* Verified db qualifier is current db*/
	IF (@vc1 is not null and @vc1 <> db_name())
	begin
		raiserror(15302,-1,-1)  --Do not qualify with DB name.
		return (1)
	end

	/*  Derive OwnerName and @ObjectStatementName*/
	select	@OwnerName				=	parsename(@name, 2)
			,@ObjectStatementName	=	parsename(@name, 1)

	IF (@ObjectStatementName is NULL and @name is not null)
	begin
		raiserror(15253,-1,-1,@name)
		return (1)
	end

	/*	Copy info from sysprotects for processing	*/
	IF charindex('O',@permissionarea) > 0
	begin
		/*	Copy info for objects	*/
		INSERT	#t1_Prots
        (	Id
			,Type1Code

			,ObjType
			,ActionName
			,ActionCategory
			,ProtectTypeName

			,Columns_Orig
			,OwnerName
			,ObjectName
			,GranteeName

			,GrantorName
			,ColumnName
            ,ColId

			,Max_ColId
			,All_Col_Bits_On
			,new_Bit_On	)

	/*	1Regul indicates action can be at column level,
		2Simpl indicates action is at the object level */
		SELECT	id
				,case
					when columns is null then '2Simpl'
					else '1Regul'
				end

				,Null
				,val1.name
				,'Ob'
				,val2.name

				,columns
				,user_name(objectproperty( id, 'ownerid' ))
				,object_name(id)
				,user_name(uid)

				,user_name(grantor)
				,case
					when columns is null then '.'
					else Null
				end
				,-123

				,Null
				,Null
				,Null
		FROM	sysprotects sysp
				,master.dbo.spt_values  val1
				,master.dbo.spt_values  val2
		where	(@OwnerName is null or user_name(objectproperty( id, 'ownerid' )) = @OwnerName)
		and	(@ObjectStatementName is null or object_name(id) =  @ObjectStatementName)
		and	(@username is null or user_name(uid) =  @username)
		and	(@grantorname is null or user_name(grantor) =  @grantorname)
		and	val1.type     = 'T'
		and	val1.number   = sysp.action
		and	val2.type     = 'T' --T is overloaded.
		and	val2.number   = sysp.protecttype
		and sysp.id != 0

		IF EXISTS (SELECT * From #t1_Prots)
		begin
			UPDATE	#t1_Prots set ObjType = ob.xtype
			FROM	sysobjects    ob
			WHERE	ob.id	=  #t1_Prots.Id

			UPDATE 	#t1_Prots
			set		Max_ColId = (select max(colid) from syscolumns sysc
								where #t1_Prots.Id = sysc.id)	-- colid may not consecutive if column dropped
			where Type1Code = '1Regul'

			/*	First bit set indicates actions pretains to new columns. (i.e. table-level permission)
				Set new_Bit_On accordinglly							*/
			UPDATE	#t1_Prots SET new_Bit_On =
			CASE	convert(int,substring(Columns_Orig,1,1)) & 1
				WHEN	1 then	1
				ELSE	0
			END
			WHERE	ObjType	<> 'V'	and	 Type1Code = '1Regul'

			/* Views don't get new columns	*/
			UPDATE #t1_Prots set new_Bit_On = 0
			WHERE  ObjType = 'V'

			/*	Indicate enties where column level action pretains to all
				columns in table All_Col_Bits_On = 1					*/
			UPDATE	#t1_Prots	set		All_Col_Bits_On = 1
			where	#t1_Prots.Type1Code	 =  '1Regul'
			and	not exists
				(select *
				from syscolumns sysc, master..spt_values v
				where #t1_Prots.Id = sysc.id and sysc.colid = v.number
				and v.number <= Max_ColId		-- column may be dropped/added after Max_ColId snap-shot
				and v.type = 'P' and
			/*	Columns_Orig where first byte is 1 means off means on and on mean off
				where first byte is 0 means off means off and on mean on	*/
					case convert(int,substring(#t1_Prots.Columns_Orig, 1, 1)) & 1
						when 0 then convert(tinyint, substring(#t1_Prots.Columns_Orig, v.low, 1))
						else (~convert(tinyint, isnull(substring(#t1_Prots.Columns_Orig, v.low, 1),0)))
					end & v.high = 0)

			/* Indicate entries where column level action pretains to
				only some of columns in table  All_Col_Bits_On  =  0*/
			UPDATE	#t1_Prots	set  All_Col_Bits_On  =  0
			WHERE	#t1_Prots.Type1Code  =  '1Regul'
			and	All_Col_Bits_On  is  null

			Update #t1_Prots
			set ColumnName  =
			case
				when All_Col_Bits_On = 1 and new_Bit_On = 1 then '(All+New)'
				when All_Col_Bits_On = 1 and new_Bit_On = 0 then '(All)'
				when All_Col_Bits_On = 0 and new_Bit_On = 1 then '(New)'
			end
			from	#t1_Prots
			where	ObjType    IN ('S ' ,'U ', 'V ')
			and	Type1Code = '1Regul'
			and   NOT (All_Col_Bits_On = 0 and new_Bit_On = 0)

			/* Expand and Insert individual column permission rows */
			INSERT	into   #t1_Prots
				(Id
				,Type1Code
				,ObjType
				,ActionName

				,ActionCategory
				,ProtectTypeName
				,OwnerName
				,ObjectName

				,GranteeName
				,GrantorName
				,ColumnName
				,ColId	)
		   SELECT	prot1.Id
					,'1Regul'
					,ObjType
					,ActionName

					,ActionCategory
					,ProtectTypeName
					,OwnerName
					,ObjectName

					,GranteeName
					,GrantorName
					,col_name ( prot1.Id ,val1.number )
					,val1.number
			from	#t1_Prots              prot1
					,master.dbo.spt_values  val1
					,syscolumns sysc
			where	prot1.ObjType    IN ('S ' ,'U ' ,'V ')
				and	prot1.All_Col_Bits_On = 0
				and prot1.Id	= sysc.id
				and	val1.type   = 'P'
				and	val1.number = sysc.colid
				and
				case convert(int,substring(prot1.Columns_Orig, 1, 1)) & 1
					when 0 then convert(tinyint, substring(prot1.Columns_Orig, val1.low, 1))
					else (~convert(tinyint, isnull(substring(prot1.Columns_Orig, val1.low, 1),0)))
				end & val1.high <> 0

			delete from #t1_Prots
					where	ObjType    IN ('S ' ,'U ' ,'V ')
							and	All_Col_Bits_On = 0
							and new_Bit_On = 0
		end
	end

	/* Handle statement permissions here*/
	IF (charindex('S',@permissionarea) > 0)
	begin
	   /*	All statement permissions are 2Simpl */
		INSERT	#t1_Prots
			 (	Id
				,Type1Code
				,ObjType
				,ActionName

				,ActionCategory
				,ProtectTypeName
				,Columns_Orig
				,OwnerName

				,ObjectName
				,GranteeName
				,GrantorName
				,ColumnName

				,ColId
				,Max_ColId
				,All_Col_Bits_On
				,new_Bit_On	)
		SELECT	id
				,'2Simpl'
				,Null
				,val1.name

				,'St'
				,val2.name
				,columns
				,'.'

				,'.'
				,user_name(sysp.uid)
				,user_name(sysp.grantor)
				,'.'
				,-123

				,Null
				,Null
				,Null
		FROM	sysprotects				sysp
				,master.dbo.spt_values	val1
				,master.dbo.spt_values  val2
		where	(@username is null or user_name(sysp.uid) = @username)
			and	(@grantorname is null or user_name(sysp.grantor) = @grantorname)
			and	val1.type     = 'T'
			and	val1.number   =  sysp.action
			and	(@ObjectStatementName is null or val1.name = @ObjectStatementName)
			and	val2.number   = sysp.protecttype
			and	val2.type     = 'T'
			and sysp.id = 0
	end

	IF NOT EXISTS (SELECT * From #t1_Prots)
	begin
-- Commented out by NE - 6/12/2007
--		raiserror(15330,-1,-1)
		return (1)
	end

	/*	Calculate dynamic display col widths		*/
	SELECT
	@charMaxLenOwner       =
		convert ( varchar, max(datalength(OwnerName)))

	,@charMaxLenObject      =
		convert ( varchar, max(datalength(ObjectName)))

	,@charMaxLenGrantee     =
		convert ( varchar, max(datalength(GranteeName)))

	,@charMaxLenGrantor     =
		convert ( varchar, max(datalength(GrantorName)))

	,@charMaxLenAction      =
		convert ( varchar, max(datalength(ActionName)))

	,@charMaxLenColumnName  =
		convert ( varchar, max(datalength(ColumnName)))
	from	#t1_Prots

/*  Output the report	*/
EXECUTE(
'Set nocount off

SELECT	''Owner''		= substring (OwnerName   ,1 ,' + @charMaxLenOwner   + ')

		,''Object''		= substring (ObjectName  ,1 ,' + @charMaxLenObject  + ')

		,''Grantee''	= substring (GranteeName ,1 ,' + @charMaxLenGrantee + ')

		,''Grantor''	= substring (GrantorName ,1 ,' + @charMaxLenGrantor + ')

		,''ProtectType''= ProtectTypeName

		,''Action''		= substring (ActionName ,1 ,' + @charMaxLenAction + ')

		,''Column''		= substring (ColumnName ,1 ,' + @charMaxLenColumnName + ')
   from	#t1_Prots
   order by
		ActionCategory
		,Owner				,Object
		,Grantee			,Grantor
		,ProtectType		,Action
		,ColId  --Multiple  -123s  ( <0 )  possible

Set nocount on'
)

Return (0) -- sp_helprotect2

GO

-- Detect User permissions for all databases and users - SQL 2005 version only
-- Don't need modified version of sp_helprotect (named as sp_helprotect2) in SQL 2005.

USE master
GO

IF OBJECT_ID('rpt_security_detect_user_permissions_2005') IS NOT NULL
	DROP PROCEDURE [dbo].[rpt_security_detect_user_permissions_2005]
GO

CREATE PROCEDURE dbo.rpt_security_detect_user_permissions_2005

AS

/*

	NE 6/11/2007 - Detect User permissions for all databases and users - SQL 2005 version only.

	EXEC rpt_security_detect_user_permissions_2005

*/

SET NOCOUNT ON

CREATE TABLE #Permissions (dbName varchar(500), UserName varchar(500), owner varchar(500),object varchar(500),Grantee varchar(500),Grantor varchar(500),ProtectType varchar(500),Act varchar(500),Col varchar(500))

CREATE TABLE #Roles (dbName varchar(500), UserName varchar(500),GroupName varchar(500),LoginName varchar(500),DefDBName varchar(500), DefSchemaName varchar(500), UserID varchar(500),SID varchar(50))

DECLARE
	@dbname varchar(500),
	@sql varchar(8000)

DECLARE @temp table
(
	dbname VARCHAR(500)
)

INSERT INTO @temp
	SELECT name
	FROM master.dbo.sysdatabases WITH (NOLOCK)
	WHERE
		category IN ('0', '1','16')
		AND
		name NOT IN ('tempdb', 'model', 'pubs', 'northwind')
		AND
		name NOT LIKE 'adventurework%'
		AND DATABASEPROPERTYEX(name, 'status') = 'ONLINE'
	ORDER BY name

SELECT @dbname = MIN(dbname) FROM @temp

WHILE @dbname IS NOT NULL
BEGIN

	SET @sql =
	'use [' + @dbname + '];
	declare @users table([User] varchar(500));
	declare @currUser varchar(500);
	insert into @users

	SELECT [name]
	FROM [' + @dbname + '].dbo.sysusers WITH (NOLOCK)
	WHERE hasdbaccess = 1 AND [name] <> ''dbo''

	SELECT @currUser = min([User]) from @users;
	WHILE @currUser IS NOT NULL
	BEGIN
		insert into #Permissions
		(owner, object, Grantee, Grantor, ProtectType, Act ,Col)
		exec sp_helprotect @username = @currUser
		insert into #Roles
		(UserName, GroupName, LoginName, DefDBName, DefSchemaName, UserID, SID)
		exec sp_helpuser @name_in_db = @currUser

		update #Permissions set dbname = ''' + @dbname + ''', UserName = @currUser where dbname is null
		update #Roles set dbname = ''' + @dbname + ''' where dbname is null

		select @currUser = min([User]) from @users where [User] > @currUser
	END
	'

	EXEC(@sql)	

	SELECT @dbname = MIN(dbname) FROM @temp WHERE dbname > @dbname

END

SELECT
	dbname AS DB_Name,
	UserName,
	'Permissions' = 'SP_AddRoleMember ''' + RTRIM(GroupName)+''', '''+ RTRIM(username)+''''
FROM #Roles
UNION
SELECT
	dbname AS DB_Name,
	UserName,
	UPPER(RTRIM(ProtectType))+' '+UPPER(RTRIM(Act))+' ON ['+owner+'].['+object+']'+
	CASE
		WHEN (PATINDEX('%All%', Col)=0) AND (Col <> '.') THEN ' ('+Col+')'
		ELSE ''
	END
	 + ' TO ['+Grantee+']'
FROM #Permissions
ORDER BY dbname, UserName, Permissions DESC

drop table #Permissions
drop table #Roles

GO

--NE - 6/11/2007 - Detect Schemas to which a user belongs within a database; SQL 2005 only.

USE master
GO

IF OBJECT_ID('rpt_security_detect_user_schema_permissions_2005') IS NOT NULL
	DROP PROCEDURE [dbo].[rpt_security_detect_user_schema_permissions_2005]
GO

CREATE PROCEDURE dbo.rpt_security_detect_user_schema_permissions_2005

AS

/*

	NE 6/11/2007 - Detect User schema permissions for all databases and users - SQL 2005 only.

	EXEC rpt_security_detect_user_schema_permissions_2005

*/

DECLARE
	@dbname varchar(200),
	@sql varchar(8000)

DECLARE @temp table
(
	dbname VARCHAR(500)
)

--The following temporary table is derived from BOL...
CREATE TABLE #prmssnTypes
(Code varchar(200), PermissionName varchar(500))

CREATE TABLE #schemaPermissions
(
	DB_Name varchar(500),
	[User] varchar(500),
	SchemaName varchar(200),
	[State] varchar(200),
	PermissionName varchar(500),
	[Grantor] varchar(200)
)

INSERT INTO #prmssnTypes
SELECT 'AL', 'ALTER'
  UNION ALL
SELECT 'ALAK', 'ALTER ANY ASYMMETRIC KEY'
  UNION ALL
SELECT 'ALAR', 'ALTER ANY APPLICATION ROLE'
  UNION ALL
SELECT 'ALAS', 'ALTER ANY ASSEMBLY'
  UNION ALL
SELECT 'ALCF', 'ALTER ANY CERTIFICATE'
  UNION ALL
SELECT 'ALDS', 'ALTER ANY DATASPACE'
  UNION ALL
SELECT 'ALED', 'ALTER ANY DATABASE EVENT NOTIFICATION'
  UNION ALL
SELECT 'ALFT', 'ALTER ANY FULLTEXT CATALOG'
  UNION ALL
SELECT 'ALMT', 'ALTER ANY MESSAGE TYPE'
  UNION ALL
SELECT 'ALRL', 'ALTER ANY ROLE'
  UNION ALL
SELECT 'ALRT', 'ALTER ANY ROUTE'
  UNION ALL
SELECT 'ALSB', 'ALTER ANY REMOTE SERVICE BINDING'
  UNION ALL
SELECT 'ALSC', 'ALTER ANY CONTRACT'
  UNION ALL
SELECT 'ALSK', 'ALTER ANY SYMMETRIC KEY'
  UNION ALL
SELECT 'ALSM', 'ALTER ANY SCHEMA'
  UNION ALL
SELECT 'ALSV', 'ALTER ANY SERVICE'
  UNION ALL
SELECT 'ALTG', 'ALTER ANY DATABASE DDL TRIGGER'
  UNION ALL
SELECT 'ALUS', 'ALTER ANY USER'
  UNION ALL
SELECT 'AUTH', 'AUTHENTICATE'
  UNION ALL
SELECT 'BADB', 'BACKUP DATABASE'
  UNION ALL
SELECT 'BALO', 'BACKUP LOG'
  UNION ALL
SELECT 'CL', 'CONTROL'
  UNION ALL
SELECT 'CO', 'CONNECT'
  UNION ALL
SELECT 'CORP', 'CONNECT REPLICATION'
  UNION ALL
SELECT 'CP', 'CHECKPOINT'
  UNION ALL
SELECT 'CRAG', 'CREATE AGGREGATE'
  UNION ALL
SELECT 'CRAK', 'CREATE ASYMMETRIC KEY'
  UNION ALL
SELECT 'CRAS', 'CREATE ASSEMBLY'
  UNION ALL
SELECT 'CRCF', 'CREATE CERTIFICATE'
  UNION ALL
SELECT 'CRDB', 'CREATE DATABASE'
  UNION ALL
SELECT 'CRDF', 'CREATE DEFAULT'
  UNION ALL
SELECT 'CRED', 'CREATE DATABASE DDL EVENT NOTIFICATION'
  UNION ALL
SELECT 'CRFN', 'CREATE FUNCTION'
  UNION ALL
SELECT 'CRFT', 'CREATE FULLTEXT CATALOG'
  UNION ALL
SELECT 'CRMT', 'CREATE MESSAGE TYPE'
  UNION ALL
SELECT 'CRPR', 'CREATE PROCEDURE'
  UNION ALL
SELECT 'CRQU', 'CREATE QUEUE'
  UNION ALL
SELECT 'CRRL', 'CREATE ROLE'
  UNION ALL
SELECT 'CRRT', 'CREATE ROUTE'
  UNION ALL
SELECT 'CRRU', 'CREATE RULE'
  UNION ALL
SELECT 'CRSB', 'CREATE REMOTE SERVICE BINDING'
  UNION ALL
SELECT 'CRSC', 'CREATE CONTRACT'
  UNION ALL
SELECT 'CRSK', 'CREATE SYMMETRIC KEY'
  UNION ALL
SELECT 'CRSM', 'CREATE SCHEMA'
  UNION ALL
SELECT 'CRSN', 'CREATE SYNONYM'
  UNION ALL
SELECT 'CRSV', 'CREATE SERVICE'
  UNION ALL
SELECT 'CRTB', 'CREATE TABLE'
  UNION ALL
SELECT 'CRTY', 'CREATE TYPE'
  UNION ALL
SELECT 'CRVW', 'CREATE VIEW'
  UNION ALL
SELECT 'CRXS', 'CREATE XML SCHEMA COLLECTION'
  UNION ALL
SELECT 'DL', 'DELETE'
  UNION ALL
SELECT 'EX', 'EXECUTE'
  UNION ALL
SELECT 'IM', 'IMPERSONATE'
  UNION ALL
SELECT 'IN', 'INSERT'
  UNION ALL
SELECT 'RC', 'RECEIVE'
  UNION ALL
SELECT 'RF', 'REFERENCES'
  UNION ALL
SELECT 'SL', 'SELECT'
  UNION ALL
SELECT 'SN', 'SEND'
  UNION ALL
SELECT 'SPLN', 'SHOWPLAN'
  UNION ALL
SELECT 'SUQN', 'SUBSCRIBE QUERY NOTIFICATIONS'
  UNION ALL
SELECT 'TO', 'TAKE OWNERSHIP'
  UNION ALL
SELECT 'UP', 'UPDATE'
  UNION ALL
SELECT 'VW', 'VIEW DEFINITION'
  UNION ALL
SELECT 'VWDS', 'VIEW DATABASE STATE'

INSERT INTO @temp
	SELECT name
	FROM master.dbo.sysdatabases WITH (NOLOCK)
	WHERE
		category IN ('0', '1','16')
		AND
		name NOT IN ('tempdb', 'model', 'pubs', 'northwind')
		AND
		name NOT LIKE 'adventurework%'
		AND DATABASEPROPERTYEX(name, 'status') = 'ONLINE'
	ORDER BY name

SELECT @dbname = MIN(dbname) FROM @temp

WHILE @dbname IS NOT NULL
BEGIN

	SET @sql =
	'use [' + @dbname + ']
	declare @users table([User] varchar(500));
	declare @currUser varchar(500);
	insert into @users

	SELECT [name]
	FROM [' + @dbname + '].dbo.sysusers WITH (NOLOCK)
	WHERE hasdbaccess = 1 AND [name] <> ''dbo''

	SELECT @currUser = min([User]) from @users;
	WHILE @currUser IS NOT NULL
	BEGIN
		INSERT INTO #schemaPermissions
		(SchemaName, [State], PermissionName, [Grantor])
		SELECT
			s.name AS SchemaName,
			prmssn.state_desc as [State],
			t.PermissionName,
			ISNULL(grantor_principal.name, '''') AS [Grantor]
		FROM
			[' + @dbname + '].sys.schemas s

			INNER JOIN [' + @dbname + '].sys.database_permissions prmssn
			ON prmssn.major_id = s.schema_id
			AND prmssn.minor_id=0 AND prmssn.class=3

			INNER JOIN [' + @dbname + '].sys.database_principals grantor_principal
			ON grantor_principal.principal_id = prmssn.grantor_principal_id

			INNER JOIN [' + @dbname + '].sys.database_principals grantee_principal
			ON grantee_principal.principal_id = prmssn.grantee_principal_id

			LEFT JOIN #prmssnTypes t
			ON prmssn.type = t.Code COLLATE Latin1_General_CI_AS_KS_WS

		WHERE grantee_principal.name = @currUser

		UPDATE #schemaPermissions SET DB_Name = ''' + @dbname + ''', [User] = @currUser WHERE DB_Name IS NULL

		select @currUser = min([User]) from @users where [User] > @currUser
	END'

	EXEC(@sql)	

	SELECT @dbname = MIN(dbname) FROM @temp WHERE dbname > @dbname

END

SELECT
	DB_Name,
	[User],
	SchemaName,
	[State],
	PermissionName,
	[Grantor],
	Statement = 'USE ' + DB_Name + '; ' + [STATE] + ' ' + PermissionName + ' ON SCHEMA::[' + SchemaName + '] TO [' + [User] + ']'
FROM #schemaPermissions
ORDER BY DB_Name, [User], SchemaName, [State], PermissionName, [Grantor]

DROP TABLE #schemaPermissions
DROP TABLE #prmssnTypes

GO

Posted November 24, 2010 by Norm Enger in Microsoft SQL Server

SQL Server Security Audit Script – Detect Logins With No Permissions – SQL 2000 Version   1 comment

Originally published Thursday, July 12, 2007 at http://www.biblereadingschedule.com/nucleus?itemid=65&catid=6

This procedure displays any logins (NT or SQL logins) which have no SQL level or database permissions. This version is for SQL 2000 only. You will also find the SQL 2005 version of this in a subsequent post. These logins should be able to be dropped from SQL.

Here is the script. To easily copy the code, hover your mouse over the below section and click the “copy to clipboard” button in the tool bar that appears at the top right. If the copy button is not available, then click the “view source” button, press Ctrl + A to select all, and Ctrl + C to copy. Then paste into your favorite editor.

USE [master]
GO

IF OBJECT_ID(N'[dbo].[rpt_security_Logins_with_no_permissions_2000]') IS NOT NULL
	DROP PROCEDURE [dbo].[rpt_security_Logins_with_no_permissions_2000]
GO

CREATE PROC [dbo].[rpt_security_Logins_with_no_permissions_2000]

AS

/*

	NE - 7/11/2007 - SQL 2000 only

	Displays any SQL logins which have no server-wide or database permissions.
	These logins should be able to be dropped from the SQL server

	Original script courtesy of SQLServerCentral.com,

http://www.sqlservercentral.com/scripts/viewscript.asp?scriptid=1084

	EXEC rpt_security_Logins_with_no_permissions_2000

*/

SET NOCOUNT ON

DECLARE @username sysname
DECLARE @objname sysname
DECLARE @found integer
DECLARE @sql nvarchar(4000)
DECLARE @results TABLE (Login sysname)

SET @username = ' '
WHILE @username IS NOT NULL
BEGIN
	SELECT @username = MIN(name)
	FROM master.dbo.syslogins WITH (NOLOCK)
	WHERE sysadmin = 0
	AND securityadmin = 0
	AND serveradmin = 0
	AND setupadmin = 0
	AND processadmin = 0
	AND diskadmin = 0
	AND dbcreator = 0
	AND bulkadmin = 0
	AND name > @username
	-- this is the list of non system logins
	-- ids in server roles may not have corresponding users
	-- any database but they should not be rremoved
	SET  @found = 0

	IF @username IS NOT NULL
		BEGIN
		--  now we search through each non system database
		--  to see if this login has database access
		SET @objname = ''
		WHILE @objname IS NOT NULL
		BEGIN
			SELECT @objname = MIN( name )
			FROM master.dbo.sysdatabases WITH (NOLOCK)
			WHERE
--			name NOT IN ('master', 'model', 'msdb', 'tempdb')
--			AND
			name > @objname
			AND DATABASEPROPERTYEX(name, 'status') = 'ONLINE'

			IF @objname IS NOT NULL
			BEGIN
				SET @sql = N'SELECT @found = COUNT(*) FROM [' + @objname
				+ N'].dbo.sysusers s WITH (NOLOCK) JOIN master.dbo.syslogins x WITH (NOLOCK)
				ON s.sid = x.sid WHERE hasdbaccess = 1 AND x.name = '''+ @username + ''''

				EXEC sp_executesql @sql,N'@found Int OUTPUT',@found OUTPUT
				--SELECT @found, @objname, @username
				IF @found IS NOT NULL AND @found > 0
					SET @objname = 'zzzzz'  -- terminate as a corresponding user has been found
			END
		END

		IF @found = 0
		BEGIN
--			EXEC sp_droplogin @username
--			SELECT @username + ' was removed ' + CONVERT(varchar(23),getdate())
		INSERT INTO @results
		SELECT @username
		END
	END
END

SELECT Login
FROM @results r
ORDER BY Login

GO

--Test execution
EXEC rpt_security_Logins_with_no_permissions_2000

Posted November 24, 2010 by Norm Enger in Microsoft SQL Server