What are the minimum SQL Server Permissions required to perform a compare in Schema Compare 4.5.9? In 4.2, I needed to make sure the login/user had the SELECT permission on the view sql_expression_dependencies. That doesn't seem to be sufficient.
When I run the tool under a least privilege account, with the above permission, constraints don't appear to be getting picked up. If I run the same exact compare as a sysadmin, everything shows up as expected.
Minimum SQL Server Permissions?
-
- Posts: 4
- Joined: Mon 29 Jul 2019 18:26
Re: Minimum SQL Server Permissions?
I looked back in the forums and it looks like I documented this for 4.2.35. For 4.5.9, either VIEW DATABASE STATE or VIEW SERVER STATE is required (bold below). Be aware, that this permission grants access to data management views and can reveal queries/plans/parameters in the buffer cache.
USE [master];
GO
CREATE LOGIN [domain\CompareUser] FROM WINDOWS WITH DEFAULT_DATABASE=[master];
GRANT VIEW SERVER STATE TO [domain\CompareUser];
GO
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
USE [master];
GO
CREATE LOGIN [domain\CompareUser] FROM WINDOWS WITH DEFAULT_DATABASE=[master];
GRANT VIEW SERVER STATE TO [domain\CompareUser];
GO
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
Re: Minimum SQL Server Permissions?
We will review this and will answer you as soon as possible.