Devart Forums

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.

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

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

Thank you for updating us on this issue.