Thursday, June 29, 2006

User name, group name and thier default database

The following script will help you to list all user name, group name and thier default database. The script will help you to list all of the above on single instance but includes all databases. I use this script as starting point to fix any security issues.

set nocount on
declare @dbName sysname, -- database name
@dbid int -- database Id

IF (object_id('tempdb..#userDetails') IS not Null)
Drop Table #userDetails
-- create temp table to hold info
CREATE TABLE #userDetails
(DbName sysname,
UserName sysname,
GroupName sysname,
LoginName sysname,
UserDefaultDB sysname)

declare @dbnames table(dbid int not null primary key clustered, dbname nvarchar(100))
INSERT INTO @dbnames(dbid, dbname)
select dbid, name from master.dbo.sysdatabases where dbid > 4 and name not like '%Sharepoint%'
select @dbid = max(dbid) from @dbnames

while @dbid is not null
SELECT @dbName = dbname FROM @dbnames
WHERE dbid = @dbid
'use ' + @dbName + '
INSERT INTO #userDetails(DbName, UserName, GroupName, LoginName, UserDefaultDB)
SELECT db_name() as DBName, As UserName , case when (usg.uid is null) then ''public'' else end as GroupName ,
lo.loginname ,lo.dbname as UserDefaultDbName
from sysusers usu
(sysmembers mem inner join sysusers usg on mem.groupuid = usg.uid) on usu.uid = mem.memberuid
join master.dbo.syslogins lo on usu.sid = lo.sid
where (usu.islogin = 1 and usu.isaliased = 0 and usu.hasdbaccess = 1)
and (usg.issqlrole = 1 or usg.uid is null)

select @dbid = max(dbid) from @dbnames
where dbid < @dbid end select * from #userDetails order by DbName, UserName, GroupName asc

No comments: