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