August 6, 2013 at 7:28 am
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
August 6, 2013 at 7:36 am
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/
August 6, 2013 at 8:31 am
D'OH!
Sometimes the simple solutions stare you in the face, and you still don't see them...
Thanks!
August 6, 2013 at 8:34 am
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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy