Wednesday, February 9, 2011

Query to get Users from SQL Server 2008

CREATE TABLE #TEMP(
UserName nvarchar(100),
RoleName nvarchar(50),
LoginName nvarchar(50),
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