Find hard coded strings in SQL: can it be done programatically?

[Lokean]

The problem:

Company was bought out and we are bringing everything into complience. Passwords are not secure and do not need to be.(required by software we are using)

Old passwords *may or may not have been hard coded* in SQL SERVER database to validate (each account was setup with the same generic password).

Need to check each proc or function for presense of hard-coded password.

We have hundreds of functions that may or may not have this hard coded password.

Is there a way to cycle through each proc and function to search for the presence of the password?

[debasisdas]

Try to search for the same in the procedure and function body text.

[Lokean]

Try to search for the same in the procedure and function body text.

There's the rub.

I don't know how to do that. I'm not a database programmer. I'm a .net programmer, I haven't gotten this deep into databases in over 10 years.

DB2 was the last one I had any serious hands-on with.

[ck9663]

This will list all object name and it's definition.

select o.name, definition
from sys.sql_modules m
inner join sysobjects o on o.id = m.object_id
where xtype in ('P','IF','TR') and definition like '%stringyourlookingfor%'

If the definition is NULL, the code is encrypted. This will only list all stored proc, functions and triggers. Replace the stringyourlooki ngfor as necessary.

Happy Coding

-- CK

[zachster17]

edit: n/m; i didn't see that you already know the password

You could also open up the management studio, right click on a database, click generate scripts, and script all objects to a new window (which creates the sql to re-create your whole database-sprocs and all) and just do a ctrl+f for phrases like 'pass' and 'password' if you don't know the exact password.

Zach