Systemprozeduren

“In Microsoft SQL Server versteht man als Systemprozedur eine Stored Procedures, die in der master-Datenbank gespeichert wird und mit dem Präfix ‘sp_’ beginnt”; das ist die weit verbreitete Erklärung für diese Art von Objekten. Ich lasse diese Behauptung zunächst einmal zu stehen und gehe darauf weiter unten noch einmal genauer ein! “Alle Objekte in der master-Datenbank mit diesem Präfix können aus jeder beliebigen Datenbank aufgerufen und verwendet werden.”; auch das ist eine weit verbreitete Aussage, die so – pauschal –  nicht richtig ist! Dennoch werde ich für den Rest dieses Abschnitts auch weiterhin den Ausdruck der “Systemprozedur” verwenden.

Die Abbildung zeigt einen Ausschnitt aus der master-Datenbank. Systemprozeduren sind im sys-Schema gespeichert. In dieses Schema können keine eigenen Objekte gespeichert werden.

Der Versuch wird mit einem entsprechenden Fehler quittiert. Systemprozeduren werden jedoch auch im dbo-Schema erkannt. Dort können eigene Tabellen / Views / Prozeduren hinterlegt werden.

Systemprozeduren unterscheiden sich von “normalen” Prozeduren durch die Möglichkeit des Aufrufs. Eine Systemprozedur kann unter bestimmten Voraussetzungen aus jedem beliebigen Kontext und aus jeder beliebigen Benutzerdatenbank aufgerufen werden. Dieses Verfahren habe ich bereits im Artikel “Optimierung von Datenbankmodellen – Richtige Wahl von Datentypen und Indexen (2)” beschrieben, in dem es darum ging, Informationen aus DBCC IND in einer Tabelle zu speichern, um die Daten nachträglich zu analysieren. Diesen Tipp habe ich während meiner Vorbereitungen zum Microsoft Certified Master von Kimberly L. Tripp (Blog) übernommen.

sp_ = Systemprozedur?

Sehr häufig wird behauptet, dass Prozeduren, deren Namen mit “sp_” eingeleitet werden, automatisch Systemprozeduren sind. Das ist aber nicht der Fall;  diese Aussage resultiert aus der Tatsache, dass Microsoft bereits in früheren Versionen von Microsoft SQL Server Systemobjekte mit dem Präfix sp_ benannt hat. Irgendwie hat sich dadurch der Eindruck verstärkt, dass Objekte dieser Art immer Systemobjekte sind. Das nachfolgende Beispiel zeigt, dass nicht nur der Name sondern – besonders – der Speicherort wesentlich zur Behauptung beiträgt.

USE master;

GO

CREATE PROC dbo.sp_demo

AS

    PRINT 'Ich bin eine Prozedur aus master'

GO

GRANT EXECUTE ON dbo.sp_demo TO public;

GO

USE demo_db;

GO

CREATE SCHEMA sp AUTHORIZATION dbo;

GO

CREATE PROC dbo.sp_demo

AS

    PRINT 'Ich bin eine Prozedur im Schema [dbo] aus demo_db'

GO

CREATE PROC sp.sp_demo

AS

    PRINT 'Ich bin eine Prozedur im Schema [sp] aus demo_db';

GO

GRANT EXECUTE ON dbo.sp_demo TO public;

GRANT EXECUTE ON sp.sp_demo TO public;

GO

CREATE USER sp_user WITHOUT LOGIN WITH DEFAULT_SCHEMA = [sp];

GO

Das Skript erzeugt in der master-Datenbank ein Objekt mit dem Namen [sp_demo] im [dbo]-Schema und gewährt die Berechtigung zur Ausführung für alle Benutzer. Anschließend werden zwei weitere Objekte mit gleichem Namen in der Datenbank [demo_db] erzeugt. Eines der genannten Objekte wird in dem zuvor erstellten Schema [sp] erstellt. Nachdem die Rechte an den Objekten in der Datenbank erteilt wurden, wird ein Testbenutzer für die Demonstration erstellt. Dieser Testbenutzer verwendet als Standardschema [sp]! Zunächst wird “die Prozedur” als dbo ausgeführt. Das Ergebnis sieht wie folgt aus:

Es ist zu erkennen, dass nicht die “Systemprozedur” aus der master-Datenbank ausgeführt wurde.

Das Standardschema des Datenbankbenutzers [dbo] ist das Schema [dbo]. Im Standardschema des angemeldeten Benutzers wird zuerst nach einem Objekt gesucht, wenn keine voll qualifizierte Objektangabe  ([Schema].[objectname]) gemacht wird. Da ein Schema nicht explizit angegeben wurde, wird die Prozedur aus dem Standardschema ausgeführt. Gleiches Spiel – aber diesmal für den Benutzer sp_user:

Im gezeigten Skript wird im Kontext des zuvor erstellten Datenbankbenutzers sp_user die Prozedur [sp_demo] in zwei unterschiedlichen Varianten ausgeführt. Die Prozedur wird ohne Schema-Qualifikation aufgerufen und das Ergebnis zeigt, dass die Prozedur aus dem Schema [sp] ausgeführt wurde. Im zweiten Beispiel wird das Schema angegeben und somit wird Microsoft SQL Server angewiesen, unmittelbar im dbo-Schema nach dem Objekt zu suchen und es auszuführen. Nun wird das Objekt [sp].[sp_demo] aus der Datenbank gelöscht und der Benutzer sp_user führt erneut die obige Befehlsreihenfolge aus.

-- Ausführung als dbo

USE demo_db;

GO

DROP PROC sp.sp_demo;

GO

EXECUTE AS User = 'sp_user';

EXEC sp_demo;

EXEC dbo.sp_demo;

REVERT;

Das Ergebnis zeigt, dass die weiter oben beschriebene Aufrufreihenfolge funktioniert. Die Prozedur [sp].[sp_demo] wurde gelöscht und wenn der Datenbankbenutzer nun die Ausführung startet, stellt Microsoft SQL Server fest, dass ein Objekt im Standardschema nicht existiert. Es folgt der nächste Aufruf aus dem Schema [dbo]. Dort wird die Prozedur gefunden und ausgeführt. Das Ergebnis ist die Meldung in der ersten Zeile. Sie ist identisch mit dem Ergebnis des zweiten Aufrufs, der das Schema explizit angibt. Ist jedoch in der Datenbank kein Objekt mit dem Namen vorhanden, sieht das Ergebnis anders aus:

USE demo_db;

GO

DROP PROC dbo.sp_demo;

GO

EXECUTE AS User = 'sp_user';

EXEC sp_demo;

EXEC dbo.sp_demo;

REVERT;

Da nun in der Benutzerdatenbank weder ein gültiges Objekt im Standardschema des Datenbankbenutzers noch im dbo-Schema vorhanden ist, sucht Microsoft SQL Server in der master-Datenbank.

Dieses Verfahren funktioniert nur mit Objekten, die das Präfix ‘sp_’ besitzen. Bei diesem Verhalten jedoch von “Systemprozeduren” auszugehen, ist nicht korrekt wie der nächste Abschnitt deutlich zeigt.

Ausführungskontext einer Prozedur in master-Datenbank

Die Stored Procedures werden nicht unter dem Knoten [Gespeicherte Systemprozeduren] angezeigt, sondern sind “normale”Prozeduren”. Genau dieser Umstand führt aber dazu, dass in den Prozeduren verwendete Systemobjekte nicht korrekt funktionieren. Wie erwähnt, sollte die von mir entwickelte Prozedur Indexstatistiken aus meiner Benutzerdatenbank ermitteln und ausgeben. Das Skript – beispielhaft – dazu sieht wie folgt aus:

USE master

GO

CREATE PROC dbo.sp_IndexInformation

    @Object_Name    sysname,

    @Index_id       int

AS

    SET NOCOUNT ON;

    -- Wenn das Objekt nicht existiert, Fehlermeldung

    IF OBJECT_ID(@Object_Name) IS NULL

    BEGIN

        RAISERROR ('Das Objekt %s existiert nicht', 0, 1, @Object_Name) WITH NOWAIT;

        RETURN (0);

    END

    SELECT  db_id()                                        AS    database_name,

            QUOTENAME(s.name) + '.' + QUOTENAME(t.name)    AS    object_name,

            i.name                                         AS    index_name,

            i.index_id                                     AS    index_id,

            ps.fragment_count,

            ps.avg_fragmentation_in_percent,

            ps.avg_page_space_used_in_percent,

            ps.page_count,

            ps.record_count

    FROM    sys.schemas s INNER JOIN sys.tables t

            ON    (s.schema_id = t.schema_id) INNER JOIN sys.indexes i

            ON    (t.object_id = i.object_id) CROSS APPLY sys.dm_db_index_physical_stats

            (

                db_id(),

                i.object_id,

                i.index_id,

                NULL,

                'DETAILED'

            ) ps

    WHERE    i.object_id = OBJECT_ID(@Object_Name) AND

            i.index_id = @Index_id;

    SET NOCOUNT OFF;

    RETURN (1);

GO

Der Inhalt der Prozedur selbst ist für die Problemstellung nicht relevant – jedoch die in der Abfrage verwendeten Objekte.

Nachdem die Prozedur in der master-Datenbank erstellt wurde, wird die Prozedur in der Benutzerdatenbank [AdventureWorks2012] ausgeführt. Das Ergebnis entspricht aber nicht den Erwartungen.

USE AdventureWorks2012;

GO

EXEC sp_IndexInformation @object_name = 'sales.salesorderheader', @Index_id = 1;

Die Prozedur wird aus der master-Datenbank ausgeführt und als Ergebnis wird NICHTS zurück geliefert. Grund dafür sind mehrere Objekte, die in der Prozedur verwendet werden. Tatsächlich sind die Objekte [sys].[schemas], [sys.tables] und [sys.indexes] die Spielverderber. Alle genannten Objekte sind datenbankspezifische Objekte und werden im Kontext der Datenbank verwendet, in dem sich das aufrufende Objekt (die Stored Procedure) befindet. OBJECT_ID, DB_ID() und [sys].[dm_db_index_physical_stats] sind Systemobjekte, die nicht in einer Benutzerdatenbank gespeichert sind sondern in der Ressourcendatenbank von Microsoft SQL Server – sie können datenbankunabhängig aufgerufen werden und werden im Kontext der aktuellen Benutzerdatenbank verwendet.

Dieses Verhalten ist sehr gut zu erkennen, da KEINE Fehlermeldung ausgegeben wird, wenn die Prüfung auf Existenz des zu prüfenden Objekts stattfindet. OBJECT_ID() wird im Kontext der Benutzerdatenbank [AdventureWorks2012] aufgerufen; in dieser Datenbank befindet sich die Tabelle [sales].[salesorderheader] und es wird keine Nachricht ausgegeben.

Das dennoch kein Ergebnis ausgegeben wird, liegt daran, dass die oben genannten Objekte mittels INNER JOIN und CROSS APPLY miteinander verbunden sind. Das führt dazu, dass die Ergebnisse aus master mit dem Ergebnis von [sys].[dm_db_index_physical_stats] aus AdventureWorks2012 verbunden werden soll – das kann zu keinem Ergebnis führen.

Lösung

Die zuvor erstellte Prozedur [sys].[IndexInformation] muss von einer “normalen” Prozedur zu einer echten Systemprozedur geändert werden. Mit Hilfe der – undokumentierten – Systemprozedur [sp_MS_marksystemobject] kann ein beliebiges Objekt in der master-Datenbank zu einem Systemobjekt hochgestuft werden. “Undokumentiert” bedeutet, dass die Funktionalität von Microsoft jederzeit eingestellt und/oder geändert werden kann – also bitte immer erst in einer Entwicklungsumgebung prüfen!

[sp_MS_Marksystemobject] verwendet die Parameter @objname und @namespace, um ein Systemobjekt zu erstellen. Der Parameter @objname bestimmt das Objekt (voll qualifiziert), das zu einem Systemobjekt geändert werden soll; der Parameter @namespace ist nur relevant, wenn es gilt, einen Trigger (Server / Datenbank) in ein Systemobjekt umzuwandeln. Das nächste Skript zeigt die Umstellung sowie die Ergebnisse vor und nach der Umstellung:

USE AdventureWorks2012;

GO

-- Informationen aus sys.objects in master-Datenbank

SELECT name, type, is_ms_shipped FROM master.sys.objects WHERE name = 'sp_IndexInformation';

-- Auführung der Prozedur ohne Ergebnisse

EXEC sp_IndexInformation @object_name = 'sales.salesorderheader', @Index_id = 1;

-- Prozedur wird zu einer Systemprozedur gewandelt

USE master;

EXEC sp_MS_marksystemobject @objname = 'dbo.sp_IndexInformation', @namespace = NULL;

USE AdventureWorks2012;

-- Ausführung der Prozedur mit Ergebnissen

EXEC sp_IndexInformation @object_name = 'sales.salesorderheader', @Index_id = 1;

-- Informationen aus sys.objects in master-Datenbank

SELECT name, type, is_ms_shipped FROM master.sys.objects WHERE name = 'sp_IndexInformation';

Bei der ersten Ausführung der Prozedur werden noch keine Ergebnisse gezeigt, nachdem aber die Prozedur zu einer Systemprozedur umgewandelt wurde, werden die Ergebnisse korrekt angezeigt. Die abschließende Abfrage zeigt, was “im Hintergrund” passiert – die Prozedur wird zu einer “von Microsoft ausgelieferten” Prozedur gemacht.

Herzlichen Dank fürs Lesen!

Seiten: 1· 2