Home Microsoft SQL Server DigiMailing iRN Contact
    Keyword



sys.sp_helplogins

  No additional text.


Syntax
create procedure sys.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   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

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

EXEC(
'
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  

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('#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

 
Last revision 2008RTM
News

  Query a named instance
Sybase+ASE+silent+install
SQL 2008 R2
Deprecated procedures in SQL2008
Reporting Services item-level role definitions
Create all your missing indexes
Converting datetime field
Start MSSQL Server Profiler at time
Replicating MSSQL Server views
Exploring Microsoft Sharepoint
The OLE DB provider "SQLNCLI10" for linked server indicates.
Mobile solar charger
Oracle to SQL Server replication
Cannot insert the value NULL into column
Undocumented Microsoft SQL Server 2008
VMware
Zoekmachine optimalisatie
SQL Servers hidden objects
FckEditor
Flash in Ajax
De hype die AJAX heet
Barcode scannen
MySQL; Gratis, makkelijk en snel!
PHP
sIFR; de combinatie tussen HTML en Flash