sp_helplogins [بایگانی] - برنامه نویس

Elham_gh

سه شنبه 18 بهمن 1384, 15:19 عصر

CREATE PROCEDURE sp_helplogins --- 1996/08/12 14:34

@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 work holding tables ----------------
/*Create temp tables before any DML to ensure dynamic*/

CREATE Table #tb2_PlainLogins
(
LoginName sysname collate database_default NOT Null
,SID varchar(85) collate database_default NOT Null
,DefDBName sysname collate database_default Null
,DefLangName sysname collate database_default Null
,AUser char(5) collate database_default Null
,ARemote char(7) collate database_default Null
)

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 master.dbo.sysprocesses
where spid <> @@spid
and dbid = 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.loginname
,u.name
,''User''
from
' + quotename(@c10DBName, '[') + '.dbo.sysusers u
,master.dbo.syslogins l
where
u.sid = l.sid AND isaliased=0' +
case @LoginNamePattern
when null then ''
else ' and ( l.name = N' + quotename(@LoginNamePattern , '''') + '
or l.loginname = N' + quotename(@LoginNamePattern , '''') + ')'
end
+
' UNION
Select

N' + quotename(@c10DBName, '''') + '
,l.loginname
,u2.name
,''MemberOf''
from
' + quotename(@c10DBName, '[')+ '.dbo.sysmembers m
,' + quotename(@c10DBName, '[')+ '.dbo.sysusers u1
,' + quotename(@c10DBName, '[')+ '.dbo.sysusers u2
,master.dbo.syslogins l
where
u1.sid = l.sid
and m.memberuid = u1.uid
and m.groupuid = u2.uid' +
case @LoginNamePattern
when null then ''
else ' and ( l.name = N' + quotename(@LoginNamePattern , '''') + '
or l.loginname = N' + quotename(@LoginNamePattern , '''') + ')'
end

EXECUTE(@exec_stmt)

end --loop 10

--------------- Populate plain logins work table ---------------

INSERT #tb2_PlainLogins
(
LoginName
,SID
,DefDBName
,DefLangName
,AUser
,ARemote
)
SELECT
loginname
,convert(varchar(85), sid)
,dbname
,language
,Null
,Null
from
master.dbo.syslogins
where
@LoginNamePattern is null
or name = @LoginNamePattern
or loginname = @LoginNamePattern

-- AUser

UPDATE #tb2_PlainLogins --(1996/08/12)
set
AUser = 'yes'
from
#tb2_PlainLogins
,#tb1_UA tb1
where
#tb2_PlainLogins.LoginName = tb1.LoginName
and #tb2_PlainLogins.AUser IS Null

UPDATE #tb2_PlainLogins
set
AUser =
CASE @CountSkipPossUsers
When 0 Then 'NO'
Else '?'
END
where
AUser IS Null

-- ARemote

UPDATE #tb2_PlainLogins
set
ARemote = 'YES'
from
#tb2_PlainLogins
,master.dbo.sysremotelogins rl
where
#tb2_PlainLogins.SID = rl.sid
and #tb2_PlainLogins.ARemote IS Null

UPDATE #tb2_PlainLogins
set
ARemote = 'no'
where
ARemote IS Null

------------ Optimize widths for plain Logins report ----------

SELECT
@charMaxLenLoginName =
convert ( varchar
,isnull ( max(datalength(LoginName)) ,9)
)
,@charMaxLenDBName =
convert ( varchar
, isnull (max(isnull (datalength(DefDBName) ,9)) ,9)
)
,@charMaxLenLangName =
convert ( varchar
, isnull (max(isnull (datalength(DefLangName) ,11)) ,11)
)
from
#tb2_PlainLogins

---------------- Print out plain Logins report -------------

/*** Message Handlers get confused.
Raiserror('...Logins...' ,0,1)
***/

EXECUTE(
'
Set nocount off

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

,''SID'' = convert(varbinary(85), SID)

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

,''DefLangName'' = substring (DefLangName ,1 ,'
+ @charMaxLenLangName + ')

,AUser
,ARemote
from
#tb2_PlainLogins
order by
LoginName

Set nocount on
'
)

------------ 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 ------------

/***
Raiserror('...Logins-to-Users...' ,0,1)
***/

EXECUTE(
'
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('#tb2_PlainLogins') IS NOT Null)
DROP Table #tb2_PlainLogins

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

Return @RetCode -- sp_helplogins

GO