Error dropping user

crimdoncrimdon Posts: 54 Bronze 3
edited August 9, 2016 5:31AM in SQL CI 2
In one of my databases, I have a user that is a member of db_datareader, db_datawriter and a custom role I created called db_executor

I have recently created a stored procedure that has an EXECUTE AS for this user

When I use the the CI tool to sync my Test database with a package, I get an error saying:

2016-08-03T11:10:54.8962293Z FINISHED WITH ERROR: Updating database
2016-08-03T11:10:54.8982295Z Error: Synchronization of 'Scripts.state' and 'xxx' failed: Cannot
2016-08-03T11:10:54.8992291Z alter the role 'db_executor', because it does not exist or you do not have
2016-08-03T11:10:54.8992291Z permission. Error executing the following SQL: EXEC sp_droprolemember
2016-08-03T11:10:54.8992291Z N'db_datareader', N'xxx'

For some reason (even though this users role membership has not changed), the CI tool wants to remove the user from these roles and then re-add it. Of course SQL Server will not allow this as a stored procedure requires this user to execute.

Any ideas?


  • Options

    Sorry to hear about this. I'm going to create a support ticket for you so that we can look into this in more detail. You should receive an email shortly.
    Software Engineer
    Redgate Software
Sign In or Register to comment.