SQL Server 2012 Error 9512 when modifying user login

Question

We are attempting to grant access to a database to one of our employees. We open up his user properties in Security -> Logins, navigate to the User Mappings page, select the database, select db_owner in the options below, set the default schema to dbo, then when we click OK to save the changes, we get the following error:

Create failed for User 'domain\username'. (Microsoft.SqlServer.Smo)
Xml data type is not supported as a parameter to remote calls. (Microsoft SQL Server, Error: 9512)

Any thoughts on why we may be getting this error and what we can do to resolve it?

Answer 1

Sounds like you have a DDL trigger that is set to listen for ALTER_LOGIN events, and is trying to pass EVENTDATA() directly to some remote procedure.

Check for this using:

USE master;
GO
SELECT OBJECT_DEFINITION([object_id]) 
  FROM sys.server_triggers
  WHERE OBJECT_DEFINITION([object_id]) LIKE N'%ALTER[_]LOGIN%';

It could also be a database-level DDL trigger that listens for ALTER_USER:

USE the_relevant_database;
GO
SELECT OBJECT_DEFINITION([object_id]) 
  FROM sys.triggers
  WHERE parent_class = 0
  AND OBJECT_DEFINITION([object_id]) LIKE N'%ALTER[_]USER%';