VS2010 DB Compare fails.. Reverse Engineer my @$$

This gave me much grief. In the end we ran a trace to find the last sql command and found this nugget of joy.

If you get hung up on:

"The Reverse Engineer operation cannot continue because you have been denied View Definition permission on at least one object in the '...' database." 
Run the following, and grant accordingly.
/*
VS2010 DB Compare fails with:
"The Reverse Engineer operation cannot continue because you have been denied View Definition permission on at least one object in the '...' database."

User is in a role that has view definition on sys and dbo, but thats not enough, apparently

GOOD
HasBaselinePermissions IsDeniedObjectPermissions
1      0

BAD
HasBaselinePermissions IsDeniedObjectPermissions
0      0

To fix:
USE master
GRANT VIEW ANY DEFINITION TO [randomSQLUser]
--REVOKE VIEW ANY DEFINITION TO [randomSQLUser]
*/
USE master
EXECUTE AS LOGIN = 'randomSQLUser' ;
select * From fn_my_permissions(NULL, NULL)
SELECT
    CAST(CASE WHEN HAS_PERMS_BY_NAME(NULL, 'DATABASE', 'VIEW DEFINITION') = 1
              THEN 1
              ELSE 0
         END AS BIT) AS 'HasBaselinePermissions'
  , CAST(CASE WHEN 1 = IS_MEMBER('db_owner')
                   OR 1 = IS_SRVROLEMEMBER('sysadmin')
                   OR 0 = (
                           SELECT
                            COUNT(ALL [p].[state])
                           FROM
                            [sys].[database_permissions] [p]
                            LEFT JOIN [sys].[database_principals] [pr] ON [pr].[principal_id] = [p].[grantee_principal_id]
                           WHERE
                            ([p].[type] IN ('VW', 'CL'))
                            AND [p].[state] = 'D'
                            AND ([p].[grantee_principal_id] = DATABASE_PRINCIPAL_ID()
                                 OR IS_MEMBER([pr].[name]) = 1)
                          ) THEN 0
              ELSE 1
         END AS BIT) AS 'IsDeniedObjectPermissions'  
         
REVERT;

Comments :

1
Anonymous said...
on 

Freakin' lifesaver. Thanks!