asked
Want to give basic security to developer for schema change
I have a developer and he often needs permission to do schema change. What group he should be added to: db_datareader, db_datawriter, db_owner(I am concerned about this one for sure). I want him to give permission once he needs it and then remove him from that group.
ASK SQL Server Central runs by you voting. For all helpful answers below, please indicate this by clicking on the thumbs up next to those answers. If any one answer lead to a solution, please indicate this by clicking on the check mark next that answer.
,granting db_ddladmin and db_datareader and db_datawriter did not work for the developer. His manager want to five public and dbcreator(not sure why).
he gets error "failed to create Feature class must be the owner to perform this operation"
I would not give him database owner as he would be able to completely drop the database if he wanted. You should be able to give him the db_ddladmin role and he'll be able to promote schema related changes. Resource:
https://technet.microsoft.com/en-us/library/ms189612(v=sql.105).aspx Note: I would not grant this level of permissions in a Production environment. Any changes, especially schema related, should be thoroughly tested and promoted in a control manner.
Your question makes me think you are talking about the Dev having access to make schema changes to the production server. Typically you would have a developer have full access to Dev, they can make all the changes they want, then package up the changes they need made into production into a package or set of scripts for the production DBA to run during the scheduled deployment. Otherwise I think DBO is the level the dev would make. have good backups. :)
I'd assign the specific permissions required rather than using a system role. From BOL: *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 IMPERSONATION permission on the owner of the target schema. All permissions associated with the securable that is being transferred are dropped when it is moved.* Edit: Plus what @BradleySQL said!
18 People are following this question.
