Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.
Sign up to join this community
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
Per the documentation: https://learn.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.
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().
