Post reply

i want out of sp_helplogin sp into table

  • August 19, 2011 at 7:32 am

    #390226

    Hi All,

    Please anybody provide me a script which gives the output of sp_helplogins in the table.

  • August 19, 2011 at 8:44 am

    #1370785

    sp_helplogins returns two different result sets.

    here's a table of the first result set i got from running this and scripting the table:

    /*

    SELECT *

    INTO #sp_helpLogins

    FROM OPENROWSET('SQLOLEDB','Server=DEV223;Trusted_Connection=Yes;Database=Master',

    'Set FmtOnly OFF;EXEC dbo.sp_helplogins')

    */

    CREATE TABLE [dbo].[#sp_helpLogins] (

    [LoginName] nvarchar(156) NULL,

    [SID] varbinary(85) NULL,

    [DefDBName] nvarchar(32) NULL,

    [DefLangName] nvarchar(40) NULL,

    [AUser] char(5) NULL,

    [ARemote] char(7) NULL)

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • August 19, 2011 at 11:10 am

    #1370909

    To follow up on Lowell's excellent code AND provided that you wanted the 2nd result set from sp_helplogins you could perform the following.

    1. Using SSMS, script the code of sp_helplogins as CREATE procedure.

    2. Using the above script change the name to something lets say like "helplogins_2"

    3. Then comment out the code that creates the first result set. To assist you in locating that portion of the sp_helplogins I have displayed the commented out portion:

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

    /* EXEC( -- Beginning of code to be commented out.

    '

    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

    '

    ) */ -- end of commenting out 1st result set

    --------------optimize UA report column display widths -

    Then you could rerun Lowell's code modified to use the new procedure (dbo.help_Logins_2' as

    SELECT * INTO #helpLogins2

    FROM OPENROWSET('SQLOLEDB','Server=DADSNEW;Trusted_Connection=Yes;Database=Test',

    'Set FmtOnly OFF;EXEC dbo.help_Logins_2')

    SELECT * FROM #helpLogins2

    -- DROP TABLE #helpLogins2

    Note that I created my new T-SQL with a new name (dbo.help_Logins_2), and NOT IN the master DB, but in my test DB.

    You can run Lowell's and mine in a test DB to be sure this is what you need. Now admittedly this is a crude (to say the least) bit of code, but if it does what you need, you can always go back in and refine the T-SQL to run more efficently.

    If everything seems to be going well, you have obviously overlooked something.

    Ron

    Please help us, help you -before posting a question please read[/url]
    Before posting a performance problem please read[/url]

  • August 22, 2011 at 2:52 am

    #1371288

    Msg 2760, Level 16, State 1, Procedure sp_helplogins_2, Line 460

    The specified schema name "sys" either does not exist or you do not have permission to use it.

    Im getting above error if i try to implement what you suggested

  • August 22, 2011 at 5:17 am

    #1371345

    what is the version of SQL Server you are using?

  • August 22, 2011 at 5:49 am

    #1371353

    shilpa.shankar87 (8/22/2011)

    Msg 2760, Level 16, State 1, Procedure sp_helplogins_2, Line 460

    The specified schema name "sys" either does not exist or you do not have permission to use it.

    Im getting above error if i try to implement what you suggested

    the error seems like you are not running the command as a sysadmin. you'll need more permissions to run the stuff we suggested.

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply