Wednesday, February 9, 2011

Query to get Users on SQL Server 2005

CREATE TABLE #TEMP(
UserName nvarchar(200),
GroupName nvarchar(50),
LoginName nvarchar(200),
DefDBName nvarchar(50),
DefSchemaName nvarchar(50),
UserID nvarchar(10),
SID nvarchar(200))

DECLARE @ID INT, @LAST INT
DECLARE @DB_NAME NVARCHAR(300)
DECLARE @SQL NVARCHAR(500)

SET @ID = (SELECT MIN(DATABASE_ID) FROM sys.databases)
SET @LAST = (SELECT MAX(DATABASE_ID) FROM sys.databases)

WHILE @ID <= @LAST 

 BEGIN 
   SET @DB_NAME = (SELECT NAME 
                   FROM sys.databases 
                   WHERE database_id = @ID)


    IF @DB_NAME IS NOT NULL 
      BEGIN 
        SET @SQL = 'INSERT INTO #TEMP 
        EXEC [' + @DB_NAME + '].[dbo].[sp_helpuser]' 
        EXEC (@SQL) 
      END 


  SET @ID = @ID + 1 
 END


 SELECT UserName 
 FROM #TEMP 
 WHERE UserName NOT IN 
  ('dbo', 'guest', 'sys', 'INFORMATION_SCHEMA') 
 GROUP BY UserName


 DROP TABLE #TEMP

No comments:

Post a Comment