question

vivekchandra09 avatar image
vivekchandra09 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.
securitydeveloperuser-group
4 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Grant Fritchey avatar image Grant Fritchey ♦♦ commented ·
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.
0 Likes 0 ·
vivekchandra09 avatar image vivekchandra09 commented ·
,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).
0 Likes 0 ·
vivekchandra09 avatar image vivekchandra09 commented ·
sorry typo give not five
0 Likes 0 ·
vivekchandra09 avatar image vivekchandra09 commented ·
he gets error "failed to create Feature class must be the owner to perform this operation"
0 Likes 0 ·
JohnM avatar image
JohnM answered
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.
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

BradleySQL avatar image
BradleySQL answered
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. :)
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

iainrobertson avatar image
iainrobertson answered
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!
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.