February 1, 2018 at 2:19 am
I have transactional replication in place.
Our support staff have ALTER permissions, but when they attempt to ALTER an object (like a table) that is being replicated, they get the following error:
Msg 21050, Level 14, State 1, Procedure sp_MSreplcheck_publish, Line 16 [Batch Start Line 30]
Only members of the sysadmin fixed server role or db_owner fixed database role can perform this operation. Contact an administrator with sufficient permissions to perform this operation.
Msg 3609, Level 16, State 2, Line 31
The transaction ended in the trigger. The batch has been aborted.
I understand WHY this is happening: sp_MSreplcheck_publish checks for sysadmin/owner and Raise an error it the login is neither.
But I need a workaround!
I cannot make the support staff sysadmin/owner as these permissions are too elevated. I am the only sysadmin here and cannot be the only person allowed to ALTER objects.
Does anyone have a solution OR workaround for me?
February 1, 2018 at 2:32 am
What is the actual permission they have, and at what level (database, schem, object)? What sort of alterations are the users making. Normally DDL changes really would be something that you don't want anybody but an administrator doing, and under change control.
John
February 1, 2018 at 2:39 am
We have dedicated support staff that can make these alteration - and yes it is all under change control. All changes are reviewed by me in any way. But I cannot be available 24/7/365. That is why we have the support staff.
They have the following permissions:
db_datareader
db_datawriter
CREATE
ALTER
All sufficient for what they need to do, but not on replicated objects - which is most of our objects
A workaround I saw was to disable the repl trigger (tr_MStran_alterstable) on the database - BUT then the change (ALTER) on the table is not replicated to the subscriber, so not great...
February 1, 2018 at 2:55 am
You didn't say what kind of changes they're making. And I still don't understand what exact permissions they have - perhaps you could script them out as GRANT statements, please? I'm glad it's under change control - that means changes are planned ahead and fully tested, yes? Given that, can you not just put the script in a job and schedule it to run at implementation time if you yourself aren't going to be available?
John
February 1, 2018 at 3:06 am
The changes they want to make is any Alteration on an existing table
eg. ALTER TABLE .... ALTER COLUMN....
or
ALTER TABLE... ADD ....
Their permissions are
GRANT EXECUTE TO ...<user>
GRANT INSERT,UPDATE,DELETE TO ...<user>
GRANT CREATE TABLE TO ...<user>
GRANT CREATE PROCEDURE TO ...<user>
GRANT CREATE FUNCTION TO ...<user>
GRANT ALTER TO ...<user>
GRANT SELECT TO ...<user>
February 1, 2018 at 3:16 am
You've missed the ON clause from your GRANT statements, but never mind - I think I get the gist of it. If it were my system, I'd be pleased that non-admins couldn't make changes that may break replication. I'd work around it with a scheduled job, as I explained before, or maybe elevating one user's permissions for the duration of the change window.
John
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply