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

Advertisements

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

One response to “SQL Server Security Audit Script – Detect Logins With No Permissions – SQL 2000 Version

Subscribe to comments with RSS.

  1. On Friday, May 02, 2008 06:34 pm
    Jeffrey wrote:

    Your scrips are great for auditing! I’m having trouble mapping them to sql 2000 roles to show a user’s permission via custom roles that have GRANTED, or DENY rights on them. Any suggestions on how I might go about this?
    Thanks!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: