USE Util
GO
SET NOCOUNT ON
GO
DECLARE @DBNAME SYSNAME = DB_NAME()
DECLARE @SQL VARCHAR(MAX) = 'USE ' + @DBNAME
SELECT @SQL = @SQL + ISNULL(Util.dbo.StringConcat('DROP SCHEMA [' + NAME + '];', '
GO
'), '')
FROM sys.schemas
WHERE NAME LIKE '%\%'
EXEC sp_permissions
@DbName = @DBNAME,
@AllCreate = @SQL OUTPUT
SELECT @SQL = @SQL + ISNULL(Util.dbo.StringConcat('IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE name COLLATE SQL_Latin1_General_CP1_CI_AS = N''' + NAME + ''')
CREATE USER [' + NAME + '] FOR LOGIN [' + NAME + ']
GO
GRANT CONNECT TO [' + NAME + '];
GRANT EXECUTE TO [' + NAME + '];
GRANT SHOWPLAN TO [' + NAME + '];
GRANT EXECUTE TO [' + NAME + '];
GRANT REFERENCES TO [' + NAME + '];
GRANT VIEW DEFINITION TO [' + NAME + ']
--GRANT EXECUTE ON SCHEMA::[SE] TO [' + NAME + ']
GRANT INSERT ON SCHEMA::[SE] TO [' + NAME + ']
--GRANT REFERENCES ON SCHEMA::[SE] TO [' + NAME + ']
GRANT SELECT ON SCHEMA::[SE] TO [' + NAME + ']
--GRANT VIEW DEFINITION ON SCHEMA::[SE] TO [' + NAME + ']
EXEC sp_addrolemember N''db_datareader'', N''' + NAME + '''
GRANT DELETE ON dbo.FixedLengthDefinition TO [' + NAME + ']
GRANT INSERT ON dbo.FixedLengthDefinition TO [' + NAME + ']
GRANT UPDATE ON dbo.FixedLengthDefinition TO [' + NAME + ']
GRANT DELETE ON dbo.FixedLengthDefinitionDetail TO [' + NAME + ']
GRANT INSERT ON dbo.FixedLengthDefinitionDetail TO [' + NAME + ']
GRANT UPDATE ON dbo.FixedLengthDefinitionDetail TO [' + NAME + ']
GO
', ''), '') + '
'
FROM sys.syslogins
WHERE (isntgroup = 1
OR ISNTUSER = 1)
AND sysadmin = 0
AND NAME NOT LIKE 'NT AUTHORITY\%'
AND NAME NOT LIKE 'NETWORK\%'
EXEC Util.dbo.PrintLargeText
@SQL
EXEC sp_execbatchterminated
@InputSQL = @SQL,
@DbName = @DBNAME