Search Stored Procedure Code in SQL Server

Every now and then, I need to find all stored procedures containing some string. Here’s how to do that:

SELECT
ROUTINE_NAME, ROUTINE_DEFINITION
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_DEFINITION LIKE '%foobar%' AND ROUTINE_TYPE='PROCEDURE'

Replace “foobar” with the string to search for.

Fix Orphan Users in SQL Server

Show all users without a login:

EXEC sp_change_users_login 'Report'

If you already have a login id and password for this user, fix it by doing:

EXEC sp_change_users_login 'Auto_Fix', 'user'

If you want to create a new login id and password for this user, fix it by doing:

EXEC sp_change_users_login 'Auto_Fix', 'user', 'login', 'password'