0

I have a user that needs to move a set of tables and procedures from one schema to another. He has db_datareader, db_datawriter, and db_ddladmin privileges. When he runs the below, he gets an error. When I add db_owner priviledge, he is able to execute the below with no error. If I do not want him (and his team) to have db_owner but still have the capability to transfer db objects to different schemas, how can I accomplish this?

ALTER SCHEMA newSchema TRANSFER oldSchema.objectMoving
0

2 Answers 2

1

Per the documentation: https://docs.microsoft.com/en-us/sql/t-sql/statements/alter-schema-transact-sql?view=sql-server-ver15#permissions

To transfer a securable from another schema, the current user must have CONTROL permission on the securable (not schema) and ALTER permission on the target schema.

If the securable has an EXECUTE AS OWNER specification on it and the owner is set to SCHEMA OWNER, the user must also have IMPERSONATE permission on the owner of the target schema.

All permissions associated with the securable that is being transferred are dropped when it is moved.

CONTROL on the database, schema or individual tables should work. There isn't much difference between CONTROL at the db level vs db_owner, so it might be better to temporarily grant db_owner and then remove it vs directly granting CONTROL permissions.

Auditing members of the db_owner role is a straight forward, simple process. Sorting through custom permission grants is substantially less clear.

1
  • I think I'm going to have to go this route since the ddladmin permission didn't do the job. Thanks for the response. I saw the CONTROL permission in the same link you sent. Again, thanks for the response.
    – rlphilli
    Sep 6, 2021 at 0:15
0

The ALTER SCHEMA documentation covers the permissions necessary. I would expect db_ddladmin to be sufficient, but each of the permissions required is interrogatable using sys.fn_my_permissions().

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.