SQL 2005 passwords

Question

SQL 2005 passwords

David Putman

Mr or Mrs. 500

Points: 551
More actions
November 13, 2006 at 4:00 am

#170967

Is there any way in SQL 2005 or SQL 2000, using SQL Server user authentication, that I can track when users last changed their password? I have looked in sysxlogins but I am non the wiser. I can't opt for Windows authentication which would of course be the easy solution.

Viewing 8 posts - 1 through 7 (of 7 total)

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

Ian Yates SSCoach Points: 19738 More actions · Answer 1

I haven't got as much experience with 2005, but with 2000 (although this is naughty you still can do it) you can script out the stored procs in your master DB and alter them to write to a table any information you would like. I did something like this once to get the sa password for one of those apps that insists on changing the sa password of the database, blocks out all other users and won't tell you what the sa password is <rant>how are you supposed to maintain such a closed system?? (and it is still your data!) Solution: Change the actions of the relevant stored procs in master after the app installer installs MSDE but before it connects to it to change the settings </rant>

ChiragNS One Orange Chip Points: 26137 More actions · Answer 2

hi

would DDL triggers help here? of course DDL triggers are available only in SQL 2005.

"Keep Trying"

David Putman Mr or Mrs. 500 Points: 551 More actions · Answer 3

Ian, many thanks I reckon that your idea of re-scripting the master stored procs is probably the way that I'll have to go.

Chirag, I am not up to speed with DDL triggers but will look into them, thank you.

Michael Meierruth SSChampion Points: 10051 More actions · Answer 4

Most smart DBAs will not allow you to do this. That's because the master stored procs can get updated when installing a service pack.

If you can get them to use one single place to change their password, say from a web page, then you can use an extra table to keep track.

Ian Yates SSCoach Points: 19738 More actions · Answer 5

I did say it wasn't a smart thing to do but was possible If it is done then it should be very well documented so that any service pack application can take the modified procs into account.

The web page password update idea is a good one.

yitiana SSC-Addicted Points: 478 More actions · Answer 6

Change SQL Server Password by Windows Authentication

If Builtin/Administrator is present in SQL Server, you can login with an ID which is member of Administrators group and change sa password in SQL Server. Just do as follows:

1. Login into SQL server using Windows Authentication.

2. In Object Explorer, open Security folder, open Logins folder. Right Click on sa account and go to Properties.

3. Type a new SQL sa password, and confirm it. Click OK to finish.

After restarting SQL Server and all its servers, you can log into SQL Server by sa login with new SQL sa password.

Here is an article describes 4 methods to change ms sql server password, that could help.

Perry Whittle SSC Guru Points: 233854 More actions · Answer 7

The following gets any SQL Server login that has had their account changed in the last 3 days

select [name] as SQL_User, modify_date as ModifiedDate

from sys.server_principals

where type = 'S' and

modify_date > convert(nvarchar(20), dateadd(d, -3 , getdate()),120)

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

"Ya can't make an omelette without breaking just a few eggs" 😉