|
Comments
|
|
Interesting. Using variables to create sql code also interesting.
|
|
|
Question:
How would this react if the login and password matches? Or what would be a good test case for it?
|
|
|
Good Tip!
|
|
Sara Karasik on
9/23/2009
What a fantastic hack!
|
|
|
good one
|
|
|
good one
|
|
|
nice
|
|
|
attaching the code code would be nice...
|
|
|
Yea nice info for the logins!
|
|
|
Here's the code:
SELECT 'PRINT ''Testing ' + [name] + '...''
GO
EXEC sp_password @loginame = ''' + [name] + ''',
@old='''', @new='''';
GO
' FROM sys.sql_logins;
|
|
|
This will not test for where the login and password matches. You could, however, modify the script where you specify the [name] as the old and new passwords. That would do the trick.
|
|
|
Here's the code for testing if the password is identical (this includes case) to the login name:
SELECT 'PRINT ''Testing ' + [name] + '...''
GO
EXEC sp_password @loginame = ''' + [name] + ''',
@old=''' + [name] + ''', @new=''' + [name] + ''';
GO
' FROM sys.sql_logins;
|
|
Ahmad Elayyan on
9/27/2009
Excellent
|
|
|
Great workaround to get sql login blank passwords!
|
|
|
GOOD
|
|
|
learned: about being creative in scripting - nice
but
@travis: retyping (or trying to reinvent) the code for this one time makes you learn more than copy paste ;)
|
|
|
Instructor was better about speaking slower and with more clarity.
|
|
|
Good
|
|
|
excellent video. It answered just what I was looking for.
|
|
mark mcnary on
11/16/2010
This might be more useful if it were expanded to show how it could work as a stored proc that could be called and generate email to the dbas when blank passwords were found.
|
|
|
Very useful - a quick and efficient way of auditing passwords
|
|
Jamshid Nouri on
12/21/2010
excellent
|
|
|
nice trick - gives me an idea on another issue - thanks!
|
|
|
Too fast.
|
|
Joe DeMarco on
12/21/2010
Excellent topic
|
|
|
Got lost on what the code was to generate the script used at the end.
|
|
|
Great video, this is how I find the blank passwords.
/* Check All SQL Logins Have a Password */
PRINT ''
PRINT '********************************'
PRINT 'Check SQL Logins have Passwords'
PRINT '********************************'
PRINT ''
SELECT @Statement = 'SELECT ISNULL(name,loginname) as ' +
'''SQL Logins Without Passwords''' +
' FROM syslogins WHERE password IS NULL and isntname = 0'
EXEC ( @Statement )
If @@rowcount = 0
Print 'No Blank Passwords on common logins'
Print ''
|
|
Don Weigend on
12/21/2010
Nice straight forward technique!
|
|
John Torrey on
12/22/2010
Nice tool for DBAs.
|
|
Robert Neal on
12/22/2010
This is not a good solution. Should demo a better way to audit passwords. I have never audited passwords but would like to.
|
|
|
Great video, Brian. Here is the same code only resvised to use ALTER LOGIN instead of sp_password. 2008 BOL states that sp_password is depricated.
SELECT 'PRINT ''Testing ' + name + '...''
GO
ALTER LOGIN [' + name + '] WITH PASSWORD='''' OLD_PASSWORD ='''';
GO
' FROM sys.sql_logins;
|
|
Jason Yousef on
12/22/2010
can you add the code! i'm getting errors when I run it!
|
|
|
Hussein - When the code is added in the comment box the line breaks are removed. The code will thus fail if you copy it and run it as is. You need to enter a line break immediately before and after each of the GO statements. There will thus be five separate lines of code.
|
|
Martin Miller on
12/28/2010
Nice trick for detecting blank passwords.
|
|
|
very good video
|
|
|
For an Auditing role we should also write the results to an audit table or file.
|
|
|
if we used sql security this would be very helpful.
|
|
|
Great way to audit passwords
|
|
|
The solution works but sp_password will be deprecated in future versions of SQL. Why not use the PWDCOMPARE example in BOL? http://msdn.microsoft.com/en-us/library/dd822792.aspx
|
|
|
Good
|
|
|
You would think that there woudl be a better way then to try and recreate all the accounts to find the no-password ones.
|
|
|
I like that it covers fine details of system level objects and also provides SQL scripting tips.
|
|
|
Clever
|
|
|
Great! Keep up the good work!
|
|
|
good
|
|
Jamshid Nouri on
1/27/2012
excellent demo!
|
|
|
good info
|
|
|
a bit long
|
|
hemant patel on
1/27/2012
Excellent , sweet and short!!
|
|
Dexter Jones on
1/27/2012
Nifty script - thanks, Brian!
|
|
|
Logic of the final query was a little too tricky to 'get' quickly.
|
|
|
Logic of the final query was a little too tricky to 'get' quickly.
|
|
|
Clever solution. But, it's still manual and error-prone. It would be better if it was shown how to use error-handling to populate a table with results.
|
|
Russell Todd on
1/30/2012
nice trick, probably messes up password reset schedule
|
|
|
Very tricky. Nice!
|
|
Kuljit Singh on
1/30/2012
Short and sweet
|
|
|
Liked it very much. Very simple approach for security auditing.
|