0

Documenting a database and find that I cannot see stored procedures in sys.procedures, even though I can see them in Object Explorer.

select p.name, x.value, p.modify_date
from sys.procedures p
left join sys.extended_properties x
on p.object_id = x.major_id
where p.is_ms_shipped = 0
and p.name NOT LIKE 'sp_%'
  • Maybe leave out the filter and p.name not like 'sp_%'? – Aaron Bertrand Sep 18 '14 at 16:37
  • Have you thought about not building html in SQL? Query the metadata tables as you are and use a proper language for string building/html emission? – billinkc Sep 18 '14 at 16:39
  • I agree with @billinkc I think you may be overcomplicating this problem. – Zane Sep 18 '14 at 16:41
  • I agree. This is what I happen when I start something at wee hours in the morning without coffee. I will shift my approach entirely by abandoning building html in SQL. I also tried leaving out the p.name not like 'sp_%' and it still didn't return anything. – Wayne Fitz Sep 18 '14 at 16:44
  • 1
    @RBarryYoung I've fixed that. – Aaron Bertrand Sep 18 '14 at 17:19
5

To ensure you can see the objects you are looking for:

  • Make sure you are in the right database / right instance - seems simple and silly but it happens all the time. This also includes not using things like AttachDbFileName in your connection string - this means that two different programs will actually have two separate copies of your database, so if one adds a procedure, the other will never see it.
  • Don't add unnecessary filters that don't accomplish anything (sys.procedures already filters out system objects, and if you use the sp_ prefix for your own procedures - which you shouldn't - you probably don't want that filter to leave those objects out, either).
  • Validate that you have VIEW DEFINITION access to all of the potential schemas where these objects may live. These are going to be protected in the metadata / catalog views but may still be exposed to you in certain scenarios under Object Explorer (especially if your Object Explorer connection is running under a different context than your query window(s)).

Also note that these are potential answers for future readers too, not just for the current OP (who we now know had an issue with the last bullet).

  • Database does not matter. This query will return correct result w/ or w/o filter from any DB. His only concern should be connecting to the same server in OE and query window. And there is always select name from sysobjects where type = 'p' and name NOT LIKE 'sp_%' order by name that he can use to validate his original query. – ajeh Sep 18 '14 at 20:02
  • @ajeh huh? So if he is looking under AdventureWorks in Object Explorer and running a query against master, that shouldn't matter? – Aaron Bertrand Sep 18 '14 at 20:18
  • No. DB name of the query window is obvious from the toolbar, while the server name is not visible in SSMS. If he is missing procs, than he must not be in the same server between OE and query window. – ajeh Sep 18 '14 at 21:04
  • Running select @@servername as server, db_name() as db before the main query would be a definitive answer to this hypothesis :) – ajeh Sep 18 '14 at 21:14
  • 1
    @ajeh also, I don't know what version of Management Studio you're using, but you can certainly see server name at least in the status bar here's mine, and you can also configure query windows to show it in the tab (I forget if it's shown by default; first thing I change). So saying that it's very likely they're on the wrong server, but impossible they're in the wrong database because it's so "obvious," is really kind of silly. – Aaron Bertrand Sep 18 '14 at 21:54
0

The answer to my specific instance of this issue was permissions. The DBA had to add datareader rights to the system schema on master for my user profile. Once this was done, everything worked as intended. He tried adding dbo owner rights, and granting view definition to just sys.procedures and that did not work.

The bizarre symptom is that I could still query select * from sys.all_objects, but none of the non-system stored procedures were present in the results.

  • Isn't this covered by my bullet "Validate that you have VIEW DEFINITION access..."? – Aaron Bertrand Sep 18 '14 at 22:06
  • Yes. I added this specific solution because my DBA knew it was an permissions issue, but tried giving me access to master and even a specific grant to the sys.procedures table. The trick is you have to ensure explicit access for datareader rights for the system schema. It took him over 30 minutes to figure that out. – Wayne Fitz Sep 18 '14 at 22:08
  • So "Validate that you have VIEW DEFINITION access to all of the potential schemas" wasn't clear enough but had I said "Validate that you have 'datareader rights' to all of the potential schemas" it would have been? Can you share the actual GRANT statement he issued to give you 'datareader rights' on a schema? Did you mean GRANT VIEW DEFINITION, and not datareader rights or GRANT SELECT? – Aaron Bertrand Sep 18 '14 at 22:10
  • The takeaway is you can't grant someone access to master and expect it to cover all schemas and you can't specifically grant view access to a specific sys table if the user profile doesn't have access to the sys schema. My DBA is convinced that's a bug or maybe he's just new or dumb. – Wayne Fitz Sep 18 '14 at 22:11
  • Well, granting view definition to sys.procedures means you can say EXEC sp_helptext N'sys.procedures'; to view the definition of that stored procedure, not that you can select from it. And granting select to it would still hide from the results any object for which you don't have VIEW DEFINITION. And granting object- or schema-level permissions to a user in master does not affect the login's users in any other databases. So yeah, maybe your DBA needs to brush up a bit. – Aaron Bertrand Sep 18 '14 at 22:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.