CREATE VIEW Metadata.Principals
AS
SELECT dp.NAME COLLATE SQL_Latin1_General_CP1_CI_AS AS UserName,
dp.TYPE COLLATE SQL_Latin1_General_CP1_CI_AS AS Type,
dp.Type_desc COLLATE SQL_Latin1_General_CP1_CI_AS AS TypeName,
dp.is_fixed_role AS IsFixedRole,
dp.create_date AS CreateDate,
CASE WHEN DoNotScript = 0
THEN 'IF NOT EXISTS (SELECT * FROM sys.database_principals (NOLOCK) WHERE name COLLATE SQL_Latin1_General_CP1_CI_AS = N''' + dp.name + ''')'
END COLLATE SQL_Latin1_General_CP1_CI_AS AS NotExistsSQL,
CASE WHEN DoNotScript = 0
THEN CASE dp.[Type]
WHEN 'G'
THEN 'CREATE USER [' + dp.name COLLATE SQL_Latin1_General_CP1_CI_AS + '] FOR LOGIN [' + sp.name COLLATE SQL_Latin1_General_CP1_CI_AS + ']'
+ ISNULL('WITH DEFAULT_SCHEMA = [' + dp.default_schema_name COLLATE SQL_Latin1_General_CP1_CI_AS + ']', '')
WHEN 'U'
THEN 'CREATE USER [' + dp.name COLLATE SQL_Latin1_General_CP1_CI_AS + '] FOR LOGIN [' + sp.name COLLATE SQL_Latin1_General_CP1_CI_AS + ']'
+ ISNULL('WITH DEFAULT_SCHEMA = [' + dp.default_schema_name COLLATE SQL_Latin1_General_CP1_CI_AS + ']', '')
WHEN 'S'
THEN 'CREATE USER [' + dp.name COLLATE SQL_Latin1_General_CP1_CI_AS + '] ' + ISNULL('FOR LOGIN [' + sp.name COLLATE SQL_Latin1_General_CP1_CI_AS
+ ']', 'WITHOUT LOGIN')
+ ISNULL(' WITH DEFAULT_SCHEMA=[' + dp.default_schema_name COLLATE SQL_Latin1_General_CP1_CI_AS + ']', '')
WHEN 'R'
THEN 'CREATE ROLE [' + dp.name COLLATE SQL_Latin1_General_CP1_CI_AS + ']' + ISNULL(' AUTHORIZATION [' + op.name COLLATE SQL_Latin1_General_CP1_CI_AS
+ ']', '')
END
END COLLATE SQL_Latin1_General_CP1_CI_AS AS CreateSQL,
CASE WHEN DoNotScript = 0
THEN 'IF EXISTS (SELECT * FROM sys.database_principals (NOLOCK) WHERE name COLLATE SQL_Latin1_General_CP1_CI_AS = N''' + dp.name COLLATE SQL_Latin1_General_CP1_CI_AS
+ ''')'
END COLLATE SQL_Latin1_General_CP1_CI_AS AS ExistsSQL,
CASE WHEN DoNotScript = 0 THEN CASE dp.[Type]
WHEN 'G' THEN 'DROP USER [' + dp.name COLLATE SQL_Latin1_General_CP1_CI_AS + ']'
WHEN 'U' THEN 'DROP USER [' + dp.name COLLATE SQL_Latin1_General_CP1_CI_AS + ']'
WHEN 'S' THEN 'DROP USER [' + dp.name COLLATE SQL_Latin1_General_CP1_CI_AS + ']'
WHEN 'R' THEN 'DROP ROLE [' + dp.name COLLATE SQL_Latin1_General_CP1_CI_AS + ']'
END
END COLLATE SQL_Latin1_General_CP1_CI_AS AS DropSQL,
ISNULL(sl.isntname, 0) AS IsNtName,
ISNULL(sl.isntgroup, 0) AS IsNtGroup,
ISNULL(sl.isntuser, 0) AS IsNtUser,
ISNULL(sl.sysadmin, 0) AS IsSysAdmin,
ISNULL(sl.securityadmin, 0) AS IsSecurityAdmin,
ISNULL(sl.serveradmin, 0) AS IsServerAdmin,
ISNULL(sl.setupadmin, 0) AS IsSetupAdmin,
ISNULL(sl.processadmin, 0) AS IsProcessAdmin,
ISNULL(sl.diskadmin, 0) AS IsDiskAdmin,
ISNULL(sl.dbcreator, 0) AS IsDbCreator,
ISNULL(sl.bulkadmin, 0) AS IsBulkAdmin,
dp.principal_id AS PrincipalId,
op.owning_principal_id AS PrincipalOwnerId
FROM sys.database_principals dp (NOLOCK)
LEFT OUTER JOIN sys.server_principals sp (NOLOCK) ON sp.sid = dp.sid
LEFT OUTER JOIN sys.database_principals op (NOLOCK) ON dp.owning_principal_id = op.principal_id
LEFT OUTER JOIN master.sys.syslogins sl (NOLOCK) ON sl.SID = dp.sid
CROSS APPLY (SELECT CASE WHEN dp.is_fixed_role = 1
OR dp.name COLLATE SQL_Latin1_General_CP1_CI_AS IN ('public', 'dbo', 'guest', 'INFORMATION_SCHEMA', 'sys', 'cdc') THEN 1
ELSE 0
END AS DoNotScript) d
WHERE dp.type COLLATE SQL_Latin1_General_CP1_CI_AS IN ('U', 'G', 'S', 'R')
AND dp.name COLLATE SQL_Latin1_General_CP1_CI_AS NOT IN ('dbo', 'guest', 'INFORMATION_SCHEMA', 'sys', 'cdc')