Want to re-write a query to not use a cursor...

  • I came up with a query to give me an idea when the various SQL logins on my servers might expire / require a password change or if the account has been locked out (Devs trying to test stuff as the account, and using an incorrect password...)

    Now, I know there's going to be some howling, but I wound up using a cursor to accomplish this. I know for something like this, that's a run it once in a while, a cursor isn't really such a bad thing, but I'd like to see what would be done differently to get it to work without the cursor.

    So, the basics:

    The cursor grabs non-disabled, SQL type logins from sys.server_principals, then uses LOGINPROPERTY to collect the info I want.

    The code:

    declare @user varchar(50)

    create table #userinfo (

    Username varchar(50),

    badpasswordcount int,

    badpasswordtime datetime,

    dayuntilexpire datetime,

    lockouttime datetime,

    islocked int,

    passwordlastsettime datetime,

    likelynextchange datetime

    )

    declare usr_cursor cursor for

    select name from sys.server_principals

    where type = 'S' and is_disabled = 0

    order by name

    open usr_cursor

    fetch next from usr_cursor into @user

    while @@FETCH_STATUS = 0

    begin

    insert into #userinfo

    select @user as 'User name',

    convert(int, LOGINPROPERTY(@user, 'BadPasswordCount')),

    convert(datetime, LOGINPROPERTY(@user, 'BadPasswordTime')),

    convert(datetime, LOGINPROPERTY(@user, 'DaysUntilExpiration')),

    convert(datetime, LOGINPROPERTY(@user, 'LockoutTime')),

    convert(int, LOGINPROPERTY('loginname', 'IsLocked')),

    convert(datetime, LOGINPROPERTY(@user, 'PasswordLastSetTime')),

    DATEADD(DD, 90, (convert(datetime, LOGINPROPERTY(@user, 'PasswordLastSetTime'))))

    fetch next from usr_cursor into @user

    end

    close usr_cursor

    deallocate usr_cursor

    select * from #userinfo

    drop table #userinfo;

    This is NOT a homework-type thing, this is me looking to expand my skills / knowledge, and looking for some help.

    Thanks all,

    Jason

  • This produces the same results.

    select name , convert(int, LOGINPROPERTY(name, 'BadPasswordCount')),

    convert(datetime, LOGINPROPERTY(name, 'BadPasswordTime')),

    convert(datetime, LOGINPROPERTY(name, 'DaysUntilExpiration')),

    convert(datetime, LOGINPROPERTY(name, 'LockoutTime')),

    convert(int, LOGINPROPERTY('loginname', 'IsLocked')),

    convert(datetime, LOGINPROPERTY(name, 'PasswordLastSetTime')),

    DATEADD(DD, 90, (convert(datetime, LOGINPROPERTY(name, 'PasswordLastSetTime'))))

    from sys.server_principals

    where type = 'S' and is_disabled = 0

    order by name

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • D'OH!

    Sometimes the simple solutions stare you in the face, and you still don't see them...

    Thanks!

  • No problem. Sometimes it is just the second set of eyes needed to look at the problem. Happy to help.

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Viewing 4 posts - 1 through 3 (of 3 total)

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