question

That's generally a lack of permission on the linked server side.
Check the user mappings in the linked server config are all setup correctly, and that the permissions are sufficient for whatever account is mapping to be used on the linked server to the linked object.

The error message "The SCHEMA LOCK permission was denied on the object" in Microsoft SQL Server typically occurs when a user or role does not have the necessary permissions to lock a schema object. Schema locking is used to manage concurrent access to database objects, and it requires specific permissions.

To resolve this issue, you can take the following steps:

Identify the Affected Object:

You should first identify the object (table, view, procedure, etc.) on which the schema lock permission is being denied.

Check Permissions:

Check the permissions for the user or role that is experiencing this issue. Ensure that the user or role has the necessary permissions to lock the schema object.

You can use the following command to check the permissions for a specific user or role on a schema object (replace <object_name> with the actual object name and <user_or_role> with the user or role name):

USE YourDatabaseName;

SELECT * FROM sys.fn_my_permissions('<object_name>', 'OBJECT') WHERE subentity_name = '<user_or_role>';

If the user or role does not have the necessary permissions, you will need to grant the required permissions.

Grant SCHEMA LOCK Permission:

To grant SCHEMA LOCK permission to a user or role, you can use the following command (replace <schema_name>, <object_name>, and <user_or_role> with the appropriate values):

USE YourDatabaseName;

GRANT SCHEMA LOCK ON OBJECT::[<schema_name>].<object_name> TO <user_or_role>;

Make sure you have the necessary permissions to grant the SCHEMA LOCK permission.

Check for Conflicting Permissions:

Sometimes, conflicting permissions can cause issues. Ensure that there are no other permissions or roles that are explicitly denying SCHEMA LOCK permission to the same user or role.

Refresh Permissions:

After granting or modifying permissions, it's a good practice to refresh the user's permissions by having them reconnect to the database or using the REVERT or FLUSH PRIVILEGES command, depending on your SQL Server version.

Review Locking Mechanism:

Review the locking mechanism being used in your SQL queries or application code. Ensure that the way locks are requested and managed aligns with your application's requirements and is consistent with the permissions you've granted.

Testing:

After making these changes, test the application or query that was encountering the error to confirm that the issue is resolved.

Remember to follow your organization's security and access control policies when granting permissions to users or roles. It's important to grant only the minimum necessary permissions to ensure data security and integrity.