exec SP_HELPLOGINS into a table

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

exec SP_HELPLOGINS into a table

Author

Topic

Wanderer
Master Smack Fu Yak Hacker

1168 Posts

Posted - 2004-07-02 : 06:33:34

Hi all,

I'm trying to work on a smart userid fixing utility for our environment. Part of the things I am looking at is putting the results of the SP_HELPUSER and SP_HELPLOGINS into tables, to work off of. SP_HELPUSER is easy, but SP_HELPLOGINS returns 2 resultsets.

I guess I could strip out the sql from the SP_HELPLOGINS sp, but I was thinking that using the MS SP's would be a better route?

So what I'm asking, is does anyone have a technique to store the exec sp_helplogins into a temp table?

So far I have (it fails for the sp_helplogins bit, naturally)


create table #HELPUSERINFO
(UserName sysname null,
GroupName sysname null,
LoginName varchar(20) null,
DefDBName sysname null,
UserID bigint null ,
SID Bigint null)

CREATE TABLE #HELPLOGININFO
(LoginName sysname null,
SID sysname null,
DefDBName sysname null,
DefLangName char(30) null,
AUser char(5) null ,
ARemote char(5) null)


INSERT INTO #HELPLOGININFO 
EXEC SP_HELPLOGINS
INSERT INTO #HELPUSERINFO EXEC SP_HELPUSER

SELECT * FROM #HELPUSERINFO
SELECT * FROM #HELPLOGININFO

DROP TABLE #HELPUSERINFO
DROP TABLE #HELPLOGININFO

*##* *##* *##* *##*

Chaos, Disorder and Panic ... my work is done here!

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2004-07-02 : 06:48:13

Why not make up your own SP's....and put what elements you want of the 2 system SP's...into it....ie break SP_HELPLOGINS into 2....

These system SP's are available in the Master DB....and the code can (should) be able to be copied/cut/amended into your own private SP's....

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2004-07-02 : 06:59:58

Yeah- I tried that it works.


CREATE PROCEDURE sp_helplogins_regan  --- 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

then run your code calling your new sproc


create table #HELPUSERINFO
(UserName sysname null,
GroupName sysname null,
LoginName varchar(20) null,
DefDBName sysname null,
UserID bigint null ,
SID Bigint null)

CREATE TABLE #HELPLOGININFO
(LoginName sysname null,
SID sysname null,
DefDBName sysname null,
DefLangName char(30) null,
AUser char(5) null ,
ARemote char(5) null)


INSERT INTO #HELPLOGININFO
EXEC SP_HELPLOGINS_regan
INSERT INTO #HELPUSERINFO EXEC SP_HELPUSER

SELECT * FROM #HELPUSERINFO
SELECT * FROM #HELPLOGININFO

DROP TABLE #HELPUSERINFO
DROP TABLE #HELPLOGININFO

Duane.

Wanderer
Master Smack Fu Yak Hacker

1168 Posts

Posted - 2004-07-02 : 07:03:07

Hi Andrew....

Yeah - as I mentioned, I did think about stripping the SQL from the MS SP, but I was pondering the old "do not access system tables directly" philosophy. Having said that, relaying on their SP, I suppose that if they change the underlying tables, they might also change the format of the result sets from the SP, thus meaning I would have to change my code anyway.

It would still be a useful technique to exec into 2 temp table, IF it could be done (not sure how though).

PS - congrat's on the 1000 - check the yak coral :-)

*##* *##* *##* *##*

Chaos, Disorder and Panic ... my work is done here!

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2004-07-02 : 09:27:33

with vb/asp calling the sp_hellogins....you could use the 'getnextresultset' feature....but it wouldn't be as efficient as spliting the raw proc into 2.

Wanderer
Master Smack Fu Yak Hacker

1168 Posts

Posted - 2004-07-02 : 10:39:13

thanks Andrew ... if anyone is interested, I post how I decided to go, and what it looks like.

CiaO

*##* *##* *##* *##*

Chaos, Disorder and Panic ... my work is done here!

X002548
Not Just a Number

15586 Posts

Posted - 2004-07-02 : 10:57:35

Well that's always been a big pain...look at sp_help

Ever try and open that code and segregate it...

I got scared...and as mentioned, you never know what they're going to do

can you bcp out a sproc?

Never tried...

Brett

8-)

EDIT: WEll whatdya know..it worked...

Well...it only gave me the first result set...


master..xp_cmdshell 'bcp "EXEC Northwind..sp_Help Orders" queryout D:\Tax\order.txt -c -Snjros1d151\njros1d151dev -Usa -Ppwd'

So worked is a relative term....

Wanderer
Master Smack Fu Yak Hacker

1168 Posts

Posted - 2004-07-02 : 11:31:53

Brett,

I don't even know if I to carrry on thinking that route :-)


exec master..xp_cmdshell 'isql -E -S opsws148\opsws148 -d RG_Scrap_Pad -Q "exec sp_helplogins" -o c:\temp\sphelpl.txt'

and trhen trying to make some sense out of the mess...

I found a usefull starting point, sp_fixusers (by ChadMat I think) which I am modifying to quit my purposes

*##* *##* *##* *##*

Chaos, Disorder and Panic ... my work is done here!

kselvia
Aged Yak Warrior

526 Posts

Posted - 2004-07-02 : 16:45:27

This looked challenging to I took a swing at it. Change <yourservername> to your server name. I don't recommend doing this, but it is doable.



--Get the first result set from sp_helplogins without knowing column names
SELECT * into #helplogins FROM 
OPENROWSET('SQLOLEDB', 'Trusted_Connection=yes;Data Source=<yourservername>;', 
	   'SET FMTONLY OFF;EXEC master.dbo.sp_helplogins') 

--Use osql with trusted connection to get appended result sets from sp_helplogins
CREATE TABLE #results (id int identity, resultrows varchar(2000))
INSERT #results (resultrows) exec master..xp_cmdshell 'osql -E -w2000 -Q "exec sp_helplogins"'

--Parse out the Login, DbName, Username and UserOrAlias columns from second result set and store in #helpusers
SELECT Rtrim(substring(resultrows,2,header1)) LoginName,
       Rtrim(substring(resultrows,header1 + 3,header2)) DBName,
       Rtrim(substring(resultrows,header1 + header2 + 4,header3)) UserName,
       Rtrim(substring(resultrows,header1 + header2 + header3 + 5,header4)) UserOrAlias
INTO #helpusers
FROM (
	select  len(parsename(replace(ltrim(rtrim(resultrows)),' ','.'),4)) header1, 
		len(parsename(replace(ltrim(rtrim(resultrows)),' ','.'),3)) header2,
		len(parsename(replace(ltrim(rtrim(resultrows)),' ','.'),2)) header3,
		len(parsename(replace(ltrim(rtrim(resultrows)),' ','.'),1)) header4
	from #results
	where id = (select id + 1 from #results where resultrows like '%LoginName%DBName%UserName%UserOrAlias%')
     ) headers,
#results
where id > (select id + 2 from #results where resultrows like '%LoginName%DBName%UserName%UserOrAlias%')
and resultrows is not null and resultrows not like '%rows a%'

select * from #helpusers
select * from #helplogins

drop table #results,#helpusers,#helplogins

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources