Sql Compare picks up schema-level permissions on user-defined schemas, but doesn't seem to work against built-in schemas.
For example, create the following database:
USE [SQLCompareTest]
GO
CREATE ROLE [Role1]
GO
CREATE SCHEMA [Schema1] AUTHORIZATION [dbo]
GO
GRANT EXECUTE ON SCHEMA::[Schema1] TO [Role1]
GRANT EXECUTE ON SCHEMA::[DBO] TO [Role1]
GRANT EXECUTE ON SCHEMA::[Guest] TO [Role1]
and use SQLCompare to compare it to an empty database. Basically what it picks up is as follows:
PRINT N'Creating role Role1'
GO
CREATE ROLE [Role1] AUTHORIZATION [dbo]
GO
PRINT N'Creating schemata'
GO
CREATE SCHEMA [Schema1] AUTHORIZATION [dbo]
GO
PRINT N'Altering permissions on Schema1'
GO
GRANT EXECUTE ON SCHEMA:: Schema1 TO [Role1]
GO
The role's permissions on the built in DBO and Guest schemas have been ignored.
Using SqlCompare 6.2.0.0 against SQL 2005 (9.0.3042). This may be a SQL bug, since SSMS's 'Generate Scripts' wizard has the same problem.
Unfortunately SQL Compare just doesn't pick up schema-level permissions on built-in schemas at the moment - this is a known bug that's already in our system. I've added this forum thread to the bug report, but I suspect it's quite difficult to fix, so I'm not sure when it will get developer attention.
For example, create the following database:
and use SQLCompare to compare it to an empty database. Basically what it picks up is as follows:
The role's permissions on the built in DBO and Guest schemas have been ignored.
Using SqlCompare 6.2.0.0 against SQL 2005 (9.0.3042). This may be a SQL bug, since SSMS's 'Generate Scripts' wizard has the same problem.