SQLCompare generates incorrect role scripts

markarmarkar Posts: 3 New member
In our SQL Server database, we have USERS named [DOMAIN\User1], [DOMAIN\User2], etc.

We also have ROLES named [User1] and [User2], which contain the above-named users.

Now we have a third role named [ContainerRole] which contains the above named USERS:
CREATE ROLE [ContainerRole]
AUTHORIZATION [dbo]
GO
EXEC sp_addrolemember N'ContainerRole', N'DOMAIN\User1'
GO
EXEC sp_addrolemember N'ContainerRole', N'DOMAIN\User2'
GO

But when we generate scripts using SQLCompare, we actually get the ROLES in this role, not the USERS:
CREATE ROLE [ContainerRole]
AUTHORIZATION [dbo]
GO
EXEC sp_addrolemember N'ContainerRole', N'User1'
GO
EXEC sp_addrolemember N'ContainerRole', N'User2'
GO

This makes no practical difference in our production database at the moment because the permissions are the same in either case, but it causes problems in our test environments, because we drop the production users and replace them with QA users.  And it could cause problems if the roles named for the users contained other users.  Obviously, you shouldn't name roles for users, but this is the world we live in.

I suspect this is related to the IgnoreUserProperties option, which is set by default, but none of the documentation says that SQLCompare will generate incorrect Role scripts if this option is set.

We're using SQLCompare 13.

Any help, even just confirmation of this bug, would be appreciated.

Mark

Answers

  • Which version of SQL Compare 13, and which version of SQL Server? I can't reproduce the problem on 13.7.16 (the latest frequent releases build) and SQL Server 2019.

    We recently made a change to role membership addition to use the ALTER ROLE ADD MEMBER syntax where possible, so I'd expect an up to date script to be using that rather than sp_addrolemember.
    Software Developer
    Redgate Software
  • markarmarkar Posts: 3 New member
    It looks like we used Redgate.Sqlcompare.engine.dll v. 13.2.4.3926 to create the scripts and to compare going forward.

    SQLServer 2017 (RTM-CU14) (KB4484710) - 14.0.3076.1
  • Could you try out the latest version, and see if that has fixed the problem? We've recently done some work in that area, so it might well have done so; if not, I might be able to do more with a more up to date script.
    Software Developer
    Redgate Software
  • markarmarkar Posts: 3 New member
    I'll do that. It may take me a while to get that version pulled into our build machine.  Thanks for your help.
Sign In or Register to comment.