Minimum SQL Server Permissions Requirements for Schema Compare

[mango333]

I'm working on an automated schema-only database refresh in our build environment, comparing the databases in the build environment with a copy of a production database. The user that will be executing the comparison has administrative privileges on both server and instance in the build environment. I need to add the user in the Production environment with the minimum permissions possible to facilitate the comparison. Can you give me the permissions required?

So far, I have ascertained that the comparison is utilizing the sys.sql_expression_dependencies view in hidden resource database mssqlsystemresource. I'm having difficulty getting this to return correctly with anything less than Control Server, instance level permissions.

Your assistance is greatly appreciated.

[alexa]

Could you please let us know what versions of SQL Server you are working with?

[mango333]

Alexa, the version ranges from 2008R2 - 2014. I actually was able to figure this out already. Below are the minimum permissions required to perform the compare. Syncing is another issue, but I already knew that needed to be elevated on the target. Thanks!

USE [master];
GO
CREATE LOGIN [domain\CompareUser] FROM WINDOWS WITH DEFAULT_DATABASE=[master];

USE [SomeDatabase];
GO
CREATE USER [domain\CompareUser] FOR LOGIN [domain\CompareUser];
GRANT VIEW DEFINITION ON DATABASE::[SomeDatabase] TO [domain\CompareUser];
GRANT SELECT ON sys.sql_expression_dependencies TO [domain\CompareUser];
GO

[alexa]

Thank you for updating us on this issue.