Owned Schema

• LOOKUP_BI-756009

  SSCertifiable

  Points: 7286

  More actions

  September 22, 2011 at 2:29 pm

  #257723

  Is there a query to find out if a sql user or role owns a schema ?

  I know hows to find the default schema, when I try to drop a sql user or role it says that the 'user or role owns a schema'

  Which dmv or sys table can I query for this ?

• spaghettidba

  SSC Guru

  Points: 105732

  More actions

  September 23, 2011 at 2:41 am

  #1385575

  This should help:

  SELECT sc.name AS schema_name,

  dbp.name AS owning_database_principal_name,

  srp.name AS owning_server_principal_name

  FROM sys.schemas AS sc

  INNER JOIN sys.database_principals AS dbp

  ON sc.principal_id = dbp.principal_id

  INNER JOIN sys.server_principals AS srp

  ON srp.sid = dbp.sid

  -- Gianluca Sartori