SQL Server Performance

Running stored procedure in WHILE loop

Discussion in 'General DBA Questions' started by Scalpel78, May 9, 2006.

  1. Scalpel78 New Member

    Hi,
    i'm trying to loop through all the syslogins, and want to run sp_droplogin on all logins which does not have a corresponding user in any database.

    I'm trying to execute the stored procedure inside the WHILE loop, but the script sp_droplogin is only run on the first username before it exits the loop. If I comment out the procedure it will print each and everyone of the login names.

    -----------------------------------------------
    use master
    go

    declare @beforecount int
    declare @aftercount int
    SELECT @beforecount = COUNT(*) from syslogins
    declare @name varchar(50)
    declare arrow CURSOR FOR
    SELECT name from syslogins

    print 'SQL Logins before cleanup: ' + CAST(@beforecount as varchar(10))
    open arrow

    FETCH NEXT FROM arrow INTO @name

    WHILE (@@FETCH_STATUS = 0)
    BEGIN
    FETCH NEXT FROM arrow INTO @name
    --exec sp_helplogins @name

    print 'Cleaning login:' + @name
    exec sp_droplogin @name
    END
    close arrow
    deallocate arrow

    SELECT @aftercount = COUNT(*) FROM syslogins
    print 'SQL Logins after cleanup: ' + CAST(@aftercount AS varchar(10))

    print 'SQL Logins removed: ' + CAST(@beforecount - @aftercount as varchar(10))

    go

    ------------------------------------------------

    How can I get the stored procedure to run inside the WHILE loop?
  2. mmarovic Active Member

    I don't have much experience with cursor, but have you tried to define cursor as scroll? I remember on 6.5, maybe 7 it helped when you wanted to remove rows from the table that is part of select statement in cursor definition.
  3. ramkumar.mu New Member

    Hope this helps!!!

    IF object_id('SP_DropInvalidLogins') IS NOT NULL
    BEGIN
    PRINT 'Dropping Procedure SP_DropInvalidLogins'
    DROP PROCEDURE SP_DropInvalidLogins
    END
    Go

    CREATE PROCEDURE SP_DropInvalidLogins
    AS

    DECLARE @names varchar(50)

    DECLARE Cursor_DropInvalidLogins CURSOR FOR
    SELECT name
    FROM master.dbo.syslogins
    WHERE dbName IS NULL

    OPEN Cursor_DropInvalidLogins

    FETCH NEXT FROM Cursor_DropInvalidLogins INTO @names

    WHILE (@@FETCH_STATUS = 0)
    BEGIN
    PRINT 'Cleaning login:' + @names
    EXEC sp_droplogin @names
    FETCH NEXT FROM Cursor_DropInvalidLogins INTO @names
    END

    CLOSE Cursor_DropInvalidLogins
    DEALLOCATE Cursor_DropInvalidLogins

    RETURN

    Go
    PRINT 'Created Procedure SP_DropInvalidLogins'
    Go

    Thanks,
    Ram

    "It is easy to write code for a spec and walk in water, provided, both are freezed..."
  4. Scalpel78 New Member

    quote:Originally posted by ramkumar.mu

    DECLARE Cursor_DropInvalidLogins CURSOR FOR
    SELECT name
    FROM master.dbo.syslogins
    WHERE dbName IS NULL

    This helps a somewhat, but it doesn't handle the correct logins.
    In your SELECT statement you do a WHERE dbname IS NULL. All of the logins I want to remove have dbName set to 'master' (the default database for that login is 'master'), but they dont have data access to any other databases. If you rightclick a login in Enterprise Mangager, preferences, and "Database Access" there is no databases checked on the logins I want to remove.

    I've tried playing around with sp_helplogins, but have some trouble using the information returned from that stored procedure to determine if sp_droplogin should be run on that user.

  5. Adriaan New Member

    Indeed sp_helplogins would be nice to use - if only it didn't return two rowsets where you need only the second one.

    You could try to script sp_helplogins from the master database in QA, create your own sp from that, and remove the part that returns the first rowset.
  6. Scalpel78 New Member

    quote:Originally posted by Adriaan

    Indeed sp_helplogins would be nice to use - if only it didn't return two rowsets where you need only the second one.

    You could try to script sp_helplogins from the master database in QA, create your own sp from that, and remove the part that returns the first rowset.

    That sounds reasonable, but I'm not a SQL guru (yet). Any idea on how to do this?
  7. Adriaan New Member

    Start the Query Analyzer. If necessary, connect to the server. If the Object Browser doesn't show, press F8 to bring it up.

    In the Object Browser, select the Master database. Open the branch for Stored Procedures.

    Search for one called "dbo.sp_helplogins", select this, then right-click and select "Script Object to New Window As", and "Create".

    In the new window, the script starts like this:
    quote:SET QUOTED_IDENTIFIER ON
    GO
    SET ANSI_NULLS OFF
    GO

    CREATE PROCEDURE sp_helplogins --- 1996/08/12 14:34
    Before doing anything else, change "sp_helplogins" to "dbo.procMyScript".

    Press F5 to create this working copy in the master database. Now change "CREATE PROCEDURE" to "ALTER PROCEDURE".

    You can delete the section between
    "---------------- Print out plain Logins report -------------",
    and
    "------------ Optimize UA report column display widths -----------"

    ... which is the first rowset returned by the standard procedure.

    Press F5 again to implement the changes to procMyScript. Check if there are any error messages on the Messages tab!

    Now in your working script, instead of calling sp_helplogins, you call dbo.procMyScript.

    Make sure that you drop the procedure after use. Of course you can keep a copy of the script for future reference.
  8. cmdr_skywalker New Member

    try the SP_HELPTEXT SP_HELPLOGINS and retrieve the query that returns the information you need. Or save the result of the SP_HELPLOGINS and retrieve the information from there. You can then modify the cursor above to select only the record that you need.

    May the Almighty God bless us all!
    www.empoweredinformation.com
  9. Adriaan New Member

    cmdr_skywalker,

    The use of sp_helptext is probably a better suggestion than mine!

    The problem with your other suggestion is that you can't store the results of sp_helplogins, because it returns two resultsets, one after the other, and with different nrs of columns.
  10. satya Moderator

    http://www.sqlteam.com/Forums/topic.asp?TOPIC_ID=37008

    Satya SKJ
    Microsoft SQL Server MVP
    Contributing Editor & Forums Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.

Share This Page