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
BEGIN
CREATE TABLE #userDetails
(DbName sysname,
UserName sysname,
GroupName sysname,
LoginName sysname,
UserDefaultDB sysname)
END

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
begin
SELECT @dbName = dbname FROM @dbnames
WHERE dbid = @dbid
EXECUTE(
'use ' + @dbName + '
INSERT INTO #userDetails(DbName, UserName, GroupName, LoginName, UserDefaultDB)
SELECT db_name() as DBName, usu.name As UserName , case when (usg.uid is null) then ''public'' else usg.name end as GroupName ,
lo.loginname ,lo.dbname as UserDefaultDbName
from sysusers usu
join
(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

connection not closed or Long running Queries

The following code will help to find the spid's that are sitting on your server for long time. This could happen when a connection is not closed or when the queries you are running is taking long time.

SELECT spid, cmd, status, loginame, open_tran, datediff(s, last_batch, getdate ()) AS [WaitTime(s)]FROM master..sysprocesses pWHERE open_tran > 0AND spid > 50AND datediff (s, last_batch, getdate ()) > 1000ANd EXISTS (SELECT * FROM master..syslockinfo l WHERE req_spid = p.spid AND rsc_type <> 2)

Get all triggers in an instance

The following code will help you to list all triggers in an instance.

declare @dbs table(databaseId int identity(1,1) not null primary key clustered, DatabaseName nvarchar(100))
create table #TriggersinDbs
(DatabaseName nvarchar(100),
ParentObject nvarchar(300),
TriggerName nvarchar(300)
)
INSERT INTO @dbs(DatabaseName)
SELECT name from master.dbo.sysdatabases where dbid > 4

declare @dbname nvarchar(100), @counter int, @objecttype nvarchar(3)

select @counter = max(databaseId) from @dbs
SET @objecttype = 'TR'
while @counter is not null
begin
select @dbname = DatabaseName from @dbs where databaseId = @counter

exec ( ' use ' + @dbname + '
INSERT INTO #TriggersinDbs(DatabaseName, ParentObject, TriggerName)
select '''+ @dbname + ''' as DatabaseName, object_name(parent_obj) as Parent_object,
name as TriggerName from sysobjects where xtype = '''+ @objecttype +'''')

select @counter = max(databaseId) from @dbs
where databaseId < @counter end SELECT * FROM #TriggersinDbs ORDER BY DatabaseName, ParentObject, TriggerName ASC drop table #TriggersinDbs