I'm investigating how to set up a monthly report and return all users on a given SQL Server to a spreadsheet.

I will then forward this spreadsheet to all the application owners, who need to "sign off" the users.

It's like a monthly housekeeping exercise.

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-----------------------------------------

Republished from http://www.sqlserver-dba.com.