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 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply