Azure managed instance ... Could not find stored procedure 'sys.sp_MS_marksystemobject'

Question

Azure managed instance ... Could not find stored procedure 'sys.sp_MS_marksystemobject'

Gokhan Varol 111

I am a sysadmin at Azure managed instance, I am trying to create system procedures in master database and mark them as system using [sys].sp_MS_marksystemobject] procedure in Azure Managed instance but I get object not found on sp_MS_marksystemobject.

USE master
GO
EXEC [sys].[sp_MS_marksystemobject] [sp_help_revlogin];

is failing with
Msg 2812, Level 16, State 62, Line 3 Could not find stored procedure 'sys.sp_MS_marksystemobject'

SELECT name, SCHEMA_NAME(schema_id) From sys.all_objects WHERE name = 'sp_MS_marksystemobject'
is returning the objectname and sys as schema

SELECT OBJECT_ID('[sys].[sp_MS_marksystemobject]')
returns null

How can I create my own sp_* named system procedures in master database and mark them as system?

1 answer

Your answer

Answer 1

David Browne - msft 3,851

When you create a procedure with the sp_ prefix in Master it's callable in the context of any database. The only additional thing sp_MS_marksystemobject appears to do is to make the static SQL in the procedure resolve object names in the context of the current database, rather than the context of the database containing the stored procedure.

You can accomplish this without using an undocumented procedure, as dynamic SQL always resolves object names with respect to the current database, not the database containing the stored procedure.

    use master
    go
    create or alter procedure sp_test
    as
    begin
      declare @sql nvarchar(max)= N'

      select * from sys.objects

      ';
      exec sp_executesql @sql
    end

   go 
    --and execute

    exec msdb..sp_test

So all you have to do is take the body of your "system stored procedure" and paste it into a nvarchar(max) literal string, escaping any single quotes by replacing ' with '', and run it with sp_executesql.

Gokhan Varol 111 Reputation points

2020-10-30T23:28:28.12+00:00

In my test in Azure Managed instance creating an sp

use master
go
exec sp_test
as
select db_name() as db, * from sys.objects
go

then

exec db1..sp_test

returned the db1 as db but returned results from master.sys.objects , did not work as it would have if it was a proper system proc (would returned db1.sys.objects in a proper system proc)
Gokhan Varol 111 Reputation points

2020-10-30T23:28:56.247+00:00

let's says you have a systemproc as below

USE master
go
create procedure sp_test
as
select db_name(), * from sys.objects
go

and execute

exec DB_B..sp_test

the resultset would have the list of sys.objects from DB_B (not from master).
This way you can create nice code generators or other type of things without needing to write dynamic sql.

Or some other use case, lets say you have 300+ databases in the server, and you have a common code, same select from same table and so on, you deploy one system proc and you are done, you maintain 1 piece of code vs trying to keep 300+ database code in sync

I suggest you utilize that .
David Browne - msft 3,851 Reputation points

2020-10-31T15:43:58.297+00:00

See updated answer for a supported way to accomplish this.
Gokhan Varol 111 Reputation points

2020-10-31T16:54:54.4+00:00

This is defeating the purpose of a system procedures again, the idea is not needing to use dynamic sql.
David Browne - msft 3,851 Reputation points

2020-11-01T00:28:15.8+00:00

How does using dynamic SQL “defeat the purpose”? And why not prefer a supported solution over an obscure undocumented one?
Gokhan Varol 111 Reputation points

2020-11-01T02:07:25.07+00:00

Are you from Microsoft support team and this is official confirmation that marking an sp_* named stored procedure with sp_MS_marksystemobject is not supported?
I was going to open a Microsoft Premier Support incident to get this official.
I have been using sql server since 1990's, I have over 300+ sp_* system procedures and some as long as 4000 or so lines, converting them to dynamic sql will take quite a bit time for me.
Thanks
Dan Guzman 9,411 Reputation points

2020-11-01T11:25:02.643+00:00
I'm not speaking for David but many undocumented features exist in SQL Server. Using undocumented functionality without specific guidance from Microsoft is at one's own risk. Below is the comment header from the sp_MS_marksystemobject proc source.

---------------------------- sp_MS_marksystemobject ----------------------------- -- FOR INTERNAL USE ONLY ... DO NOT DOCUMENT -- -- This procedure sets a bit in sysobjects. This bit has no meaning, various -- groups (starfigther, davinci, replication) use it for different things -- MSQL makes no warranty, express or implied, on what objects will or will -- not have this bit set. Use at your own risk.

The only solution in your case other than changing the proc code will be for MS to introduce sp_MS_marksystemobject on Managed instance.
David Browne - msft 3,851 Reputation points

2020-11-01T14:26:19.833+00:00

I'm not in Support, but everything that is not documented is not supported. Since you won't find it documented here: https://learn.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/database-engine-stored-procedures-transact-sql?view=sql-server-ver15, it's not supported. That doesn't mean you can't or shouldn't use it. It just means that you can't expect a bug fix for it, and its behavior is subject to change in the future.

It also may be why it was not tested and whitelisted for use in Azure SQL Database Managed Instance. A Premier Support case can be used to confirm the support status and inquire about any plans to enable this procedure in MI.

Hearing that you "have over 300+ sp_ system procedures and some as long as 4000 or so lines, converting them to dynamic sql will take quite a bit time for me." is just the kind of customer evidence the product team needs to make the case for testing and enabling this functionality.
Gokhan Varol 111 Reputation points

2020-11-01T14:28:40.657+00:00

This procedure was not documented and worked in every version of SQL Server up to now. Managed instance should have been a good match to the local sql server instance. I'll take this up with actual Microsoft support since others can also benefit from this feature.
David Browne - msft 3,851 Reputation points

2020-11-01T14:40:09.693+00:00

Good. MI is intended to enable simple lift-and-shift scenarios, so anything that blocks migration is a fair ask.
David Browne - msft 3,851 Reputation points

2020-11-01T15:38:47.17+00:00

Also note I simplified the workaround, as simply using dynamic SQL does the trick.
Gokhan Varol 111 Reputation points

2020-11-01T15:43:20.877+00:00

Try to convert something like this using sp_executesql (dynamic sql). And remember you will have to maintain this and make changes to this throughout the years

36673-sp-script.txt
David Browne - msft 3,851 Reputation points

2020-11-01T17:15:38.823+00:00

That is a good example of what might be required. See attached: 36587-sp-script.txt
Gokhan Varol 111 Reputation points

2020-11-01T17:23:13.383+00:00

There is quite a bit more to change in this code than what is suggested, you probably need to call sp_executesql over 30 times to get it done right and even than to make a change to it later on you have to unpack all the dynamic sql to figure out how to change it (code changed within dynamic sql cannot let you know at design time of problems)
David Browne - msft 3,851 Reputation points

2020-11-01T17:28:44.223+00:00

It's work, yes, and maintenance becomes harder, but moving from static to dynamic SQL is quite mechanical; just one more level of nested batch. The parameters are a pain, and only in the stored procedure scope can you return with a numeric argument.

But it's totally fair to say that if you depend on a large number of custom system stored procedures, and you will have a large number of databases, then the effort to port them to Managed Instance may not be worth it. That defines the value of enabling that proc on MI.

Share via

Azure managed instance ... Could not find stored procedure 'sys.sp_MS_marksystemobject'

1 answer

Your answer