Technical Article

Server User Access

,

This script runs from the master database and records all the databases on the server. It then checks each database for users and records their name and the database(s) on which they have access. You can then create a nice cross tab report using any report designer (I used crystal) showing users on the databases on which they have access.

CREATE PROCEDURE usp_get_users 
AS
declare @db_name sysname
declare @db_filename nvarchar(128)
declare @SQL nvarchar(600)

-- Create temporary table to 
-- store database information
create table 
#shawndatabsesusers(
db_uservarchar(30),
dbase_name sysname NOT NULL,
)
declare c1 cursor for
SELECT name, filename FROM master.dbo.sysdatabases ORDER BY name

open c1
fetch next from c1 into @db_name, @db_filename

while @@fetch_status >= 0
begin
SELECT @SQL = 'INSERT INTO #shawndatabsesusers
SELECT name, ''' +  @db_name + ''' FROM ' + @db_name + '..sysusers where islogin = 1'
-- PRINT @SQL
-- execut @SQL to insert row for each database
 execute (@SQL)
fetch next from c1 into @db_name, @db_filename
end
deallocate c1

SELECT * FROM #shawndatabsesusers

Rate

Share

Share

Rate