<div>
select * from information_schema.routine<wbr />s where routine_type = 'PROCEDURE' and<br />
Left(Routine_Name, 3) NOT IN ('sp_', 'xp_', 'ms_')<br />
and routine_definition like '%User does not have access%'<br />
<br />

</div>


select * from information_schema.routines where routine_type = 'PROCEDURE' and
Left(Routine_Name, 3) NOT IN ('sp_', 'xp_', 'ms_')
and routine_definition like '%User does not have access%'



<div>
It gives me foill error, my mistake, I am using sql 2000 and not 2005<br />
Msg 208, Level 16, State 1, Line 1<br />
Invalid object name 'information_schema.routin<wbr />es'.
</div>


It gives me foill error, my mistake, I am using sql 2000 and not 2005
Msg 208, Level 16, State 1, Line 1
Invalid object name 'information_schema.routines'.

<div>
the statement works for sql server 2000 as well<br />
<br />
what do you get if you run <br />
<br />
sp_helptext &lt;with_a_stored_proc_name_h<wbr />ere&gt;
</div>


the statement works for sql server 2000 as well

what do you get if you run

sp_helptext <with_a_stored_proc_name_here>

<div>
<div class="content wysiwyg-content">
I am using SQL Server 2005, I need to find all procs which have the following string text in the procs<br />
<br />
User does not have access<br />
<br />
I am getting this message when I run a bat file, whcih calss a procs which in turn calls many other procs<br />
Is there any way I can find this with a query
</div>
</div>


I am using SQL Server 2005, I need to find all procs which have the following string text in the procs

User does not have access

I am getting this message when I run a bat file, whcih calss a procs which in turn calls many other procs
Is there any way I can find this with a query

How can I find all stored procedures that have a following text - User does not have access

Microsoft SQL Server 2005 is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning. It includes support for managing XML data and allows a database server to be exposed over web services using Tabular Data Stream (TDS) packets encapsulated within SOAP (protocol) requests.

Microsoft SQL Server 2005

SQL (Structured Query Language) is designed to be used in conjunction with relational database products as of a means of working with sets of data. SQL consists of data definition, data manipulation, and procedural elements. Its scope includes data insert, query, update and delete, schema creation and modification, and data access control.