SQLCompare generates incorrect role scripts
markar
Posts: 3 New member
in SQL Compare
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:
But when we generate scripts using SQLCompare, we actually get the ROLES in this role, not the USERS:
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
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
Tagged:
Answers
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.
Redgate Software
SQLServer 2017 (RTM-CU14) (KB4484710) - 14.0.3076.1
Redgate Software