SQL Server - List all SQL Server users

21 August,2008 by Jack Vamvas

SQL Server sys.server_principals returns a entry for server-level principals.  I'm investigating how to set up a monthly report and return all server-level users on a given SQL Server to an Excel spreadsheet.
I then forward a customised spreadsheet to every SQL Server instance  owners, who need to "sign off" the users. It's part of  an internal SQL Server security audit - and to maintain the  the security policy.

The focus of the report is on housekeeping. There are more detailed reports created , such as Powershell sql server security audit , which focuses on server and database level privileges.

 
The script below (SQL Server 2005), identifies the users. I will need to add another call to the Active Directory to return their actual name .

--------------------------------CODE START----------------------------------
--
SET NOCOUNT ON
DECLARE @name varchar(20)

DECLARE logon_cursor CURSOR FOR 
select name from sys.server_principals WHERE 
name LIKE 'N%' AND name NOT LIKE 'NT AUTHORITY%'

OPEN logon_cursor

FETCH NEXT FROM logon_cursor 
INTO @name

WHILE @@FETCH_STATUS = 0
BEGIN
   PRINT @name
  
   
   -- Get the next author.
FETCH NEXT FROM logon_cursor 
   INTO @name
END

CLOSE logon_cursor
DEALLOCATE logon_cursor
GO
-------------------------------CODE END-----------------------------------------

 Related Posts on sql security

Powershell sql server security audit

SQL Server – Restrict SQL Server Logons by IP with EVENTDATA and SQL Logon Trigger

How to create a SQL Server Security Audit


Author: Jack Vamvas (http://www.sqlserver-dba.com)


Share:

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

Post a comment on SQL Server - List all SQL Server users


sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer