Login Script

* This is a modified procedure of Sp_help Logins
* Run the Proc in master



USE [master]
GO

/****** Object: StoredProcedure [dbo].[PRADI] Script Date: 03/10/2011 12:10:43 ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[PRADI]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[PRADI]
GO

USE [master]
GO

/****** Object: StoredProcedure [dbo].[PRADI] Script Date: 03/10/2011 12:10:43 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

create procedure [dbo].[PRADI]

@LoginNamePattern sysname = NULL
AS

set nocount on

declare
@exec_stmt nvarchar(3550)

declare
@RetCode int
,@CountSkipPossUsers int
,@Int1 int

declare
@c10DBName sysname
,@c10DBStatus int
,@c10DBSID varbinary(85)

declare
@charMaxLenLoginName varchar(11)
,@charMaxLenDBName varchar(11)
,@charMaxLenUserName varchar(11)
,@charMaxLenLangName varchar(11)

declare
@DBOptLoading int --0x0020 32 "DoNotRecover"
,@DBOptPreRecovery int --0x0040 64
,@DBOptRecovering int --0x0080 128

,@DBOptSuspect int --0x0100 256 ("not recovered")
,@DBOptOffline int --0x0200 512
,@DBOptDBOUseOnly int --0x0800 2048

,@DBOptSingleUser int --0x1000 4096


CREATE TABLE #tb1_UA
(
LoginName sysname collate database_default NOT Null
,DBName sysname collate database_default NOT Null
,UserName sysname collate database_default NOT Null
,UserOrAlias char(8) collate database_default NOT Null
)

---------------- Initial data values -------------------

select
@RetCode = 0 -- 0=good ,1=bad
,@CountSkipPossUsers = 0


---------------- Only SA can run this -------------------


if (not (is_srvrolemember('securityadmin') = 1))
begin
raiserror(15247,-1,-1)
select @RetCode = 1
goto label_86return
end

---------------------- spt_values ----------------
-------- 'D'

select @DBOptLoading = number
from master.dbo.spt_values
where type = 'D'
and name = 'loading'

select @DBOptPreRecovery = number
from master.dbo.spt_values
where type = 'D'
and name = 'pre recovery'

select @DBOptRecovering = number
from master.dbo.spt_values
where type = 'D'
and name = 'recovering'

select @DBOptSuspect = number
from master.dbo.spt_values
where type = 'D'
and name = 'not recovered'

select @DBOptOffline = number
from master.dbo.spt_values
where type = 'D'
and name = 'offline'

select @DBOptDBOUseOnly = number
from master.dbo.spt_values
where type = 'D'
and name = 'dbo use only'

select @DBOptSingleUser = number
from master.dbo.spt_values
where type = 'D'
and name = 'single user'



--------------- Cursor, for DBNames -------------------


declare ms_crs_10_DB
Cursor local static For
select
name ,status ,sid
from
master.dbo.sysdatabases



OPEN ms_crs_10_DB


----------------- LOOP 10: thru Databases ------------------


--------------
WHILE (10 = 10)
begin --LOOP 10: thru Databases


FETCH
next
from
ms_crs_10_DB
into
@c10DBName
,@c10DBStatus
,@c10DBSID


IF (@@fetch_status <> 0)
begin
deallocate ms_crs_10_DB
BREAK
end


-------------------- Okay if we peek inside this DB now?


IF ( @c10DBStatus & @DBOptDBOUseOnly > 0
AND @c10DBSID <> suser_sid()
)
begin
select @CountSkipPossUsers = @CountSkipPossUsers + 1
CONTINUE
end


IF (@c10DBStatus & @DBOptSingleUser > 0)
begin

select @Int1 = count(*)
from sys.dm_exec_requests
where session_id <> @@spid
and database_id = db_id(@c10DBName)

IF (@Int1 > 0)
begin
select @CountSkipPossUsers = @CountSkipPossUsers + 1
CONTINUE
end
end


IF (@c10DBStatus &
(
@DBOptLoading
| @DBOptRecovering
| @DBOptSuspect
| @DBOptPreRecovery
)
> 0
)
begin
select @CountSkipPossUsers = @CountSkipPossUsers + 1
CONTINUE
end


IF (@c10DBStatus &
(
@DBOptOffline
)
> 0
)
begin
--select @CountSkipPossUsers = @CountSkipPossUsers + 1
CONTINUE
end

IF (has_dbaccess(@c10DBName) <> 1)
begin
raiserror(15622,-1,-1, @c10DBName)
CONTINUE
end



--------------------- Add the User info to holding table.
select @exec_stmt = '
INSERT #tb1_UA
(
DBName
,LoginName
,UserName
,UserOrAlias
)
select
N' + quotename(@c10DBName, '''') + '
,l.name
,u.name
,''User''
from
' + quotename(@c10DBName, '[') + '.sys.sysusers u
,sys.server_principals l
where
u.sid = l.sid' +
case
when @LoginNamePattern is null
then ''
else ' and ( l.name = N' + quotename(@LoginNamePattern , '''') + '
or l.name = N' + quotename(@LoginNamePattern , '''') + ')'
end
+
' UNION
select

N' + quotename(@c10DBName, '''') + '
,l.name
,u2.name
,''MemberOf''
from
' + quotename(@c10DBName, '[')+ '.sys.database_role_members m
,' + quotename(@c10DBName, '[')+ '.sys.database_principals u1
,' + quotename(@c10DBName, '[')+ '.sys.database_principals u2
,sys.server_principals l
where
u1.sid = l.sid
and m.member_principal_id = u1.principal_id
and m.role_principal_id = u2.principal_id' +
case
when @LoginNamePattern is null
then ''
else ' and ( l.name = N' + quotename(@LoginNamePattern , '''') + '
or l.name = N' + quotename(@LoginNamePattern , '''') + ')'
end

EXECUTE(@exec_stmt)

end --loop 10

------------ Optimize UA report column display widths -----------


select
@charMaxLenLoginName =
convert ( varchar
,isnull ( max(datalength(LoginName)) ,9)
)
,@charMaxLenDBName =
convert ( varchar
,isnull ( max(datalength(DBName)) ,6)
)
,@charMaxLenUserName =
convert ( varchar
,isnull ( max(datalength(UserName)) ,8)
)
from
#tb1_UA



------------ Print out the UserOrAlias report ------------

EXEC(
'
set nocount off


select
''LoginName'' = substring (LoginName ,1 ,'
+ @charMaxLenLoginName + ')

,''DBName'' = substring (DBName ,1 ,'
+ @charMaxLenDBName + ')

,''UserName'' = substring (UserName ,1 ,'
+ @charMaxLenUserName + ')

,UserOrAlias
from
#tb1_UA
order by
1 ,2 ,3


Set nocount on
'
)


----------------------- Finalization --------------------
label_86return:


IF (object_id('#tb1_UA') IS NOT Null)
DROP Table #tb1_UA

Return @RetCode
GO