1

If I create schemata using...

CREATE SCHEMA [schema_name] AUTHORIZATION dbo

...is it possible to obtain a list of these user-defined schemata?

I can see that there's an INFORMATION_SCHEMA.SCHEMATA view but this doesn't offer any indication that I defined the schema.

There's also a sys.schemas view which lists the principal_id as 'dbo' - is it safe to assume that all schemata which reference 'dbo' as the principal_id are user-defined, or is there a safer means?

  • SQL has two schemas that ship. sys which is where most of the system views/tables/functions live. and dbo which is the default schema for user defined objects. Any schema other than that must have been created by someone. Can you elaborate on what you are trying to do? – Jonathan Fite Jun 20 at 11:57
2

This query will show you any schema which includes objects shipped by Microsoft:

SELECT s.name
FROM sys.schemas s
WHERE EXISTS (
    SELECT 1
    FROM sys.objects o
    WHERE o.is_ms_shipped = 1
        AND o.schema_id = s.schema_id
    )
ORDER BY s.name;

If you run that, you'll see dbo and sys listed.

This query shows the owner for each schema present in the current database:

SELECT [Schema] = s.name
    , [Owner] = dp.name
FROM sys.schemas s
    INNER JOIN sys.database_principals dp ON s.principal_id = dp.principal_id
ORDER BY s.name;

When a schema is created, the "owner" of the schema is either the users default schema or dbo, unless a schema owner is declared in the schema definition. Take for example:

USE MaxTest;
GO
CREATE USER SchemaOwner WITHOUT LOGIN;
GO
CREATE SCHEMA MaxSchema AUTHORIZATION SchemaOwner;
GO

SELECT [Schema] = s.name
    , [Owner] = dp.name
FROM sys.schemas s
    INNER JOIN sys.database_principals dp ON s.principal_id = dp.principal_id
WHERE s.name = N'MaxSchema';
GO
DROP SCHEMA MaxSchema;
DROP USER SchemaOwner;

The output shows:

╔═══════════╦═════════════╗
║  Schema   ║    Owner    ║
╠═══════════╬═════════════╣
║ MaxSchema ║ SchemaOwner ║
╚═══════════╩═════════════╝

For schemas that have been created recently, you can use the default trace to see who created that schema.

First step is to gather the default trace events into a temp table:

DECLARE @trcfilename nvarchar(260);

SELECT @trcfilename = path 
FROM sys.traces 
WHERE is_default = 1;

IF OBJECT_ID(N'tempdb..#trctemp', N'U') IS NOT NULL
BEGIN
    DROP TABLE #trctemp;
END

SELECT *
INTO #trctemp
FROM sys.fn_trace_gettable(@trcfilename, default) tt

Once you've got the details in the #trctemp table, use the following query to show schema create events, along with the login that created the schema:

SELECT [Login] = sp.name
    , ObjectName
    , DatabaseName
    , tt.StartTime
    , EventSubClass = CASE tt.EventSubClass 
        WHEN 0 THEN 'Begin'
        WHEN 1 THEN 'Commit'
        ELSE 'Unknown'
      END
FROM #trctemp tt
    LEFT JOIN sys.trace_subclass_values tsv ON tt.EventClass = tsv.trace_event_id 
        AND tt.EventSubClass = tsv.subclass_value
    LEFT JOIN sys.server_principals sp ON CONVERT(varbinary, tt.LoginSid, 0) = sp.sid
WHERE tt.EventClass = 46 --Object Created
    AND tt.ObjectType = 17235 --Schema
    AND tt.DatabaseID = DB_ID()
ORDER BY tt.EventSequence;

sys.trace_subclass_values is deprecated, and may go away in a future version of SQL Server. However, it is unlikely that it will go away anytime soon.

Output looks like:

╔═════════════╦════════════╦══════════════╦═════════════════════════╦═══════════════╗
║    Login    ║ ObjectName ║ DatabaseName ║        StartTime        ║ EventSubClass ║
╠═════════════╬════════════╬══════════════╬═════════════════════════╬═══════════════╣
║ Domain\User ║ MaxSchema  ║ MaxTest      ║ 2019-06-20 08:26:01.223 ║ Begin         ║
║ Domain\User ║ MaxSchema  ║ MaxTest      ║ 2019-06-20 08:26:01.223 ║ Commit        ║
║ Domain\User ║ MaxSchema  ║ MaxTest      ║ 2019-06-20 08:26:26.747 ║ Begin         ║
║ Domain\User ║ MaxSchema  ║ MaxTest      ║ 2019-06-20 08:26:26.747 ║ Commit        ║
╚═════════════╩════════════╩══════════════╩═════════════════════════╩═══════════════╝

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.