developer permissions | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

developer permissions

Hi All I’m setting up a new installation of sql server2005 as the database with all development being done with visual studio 2005 and foundation server. We have decided to implement groups with active directory for permission control of our developers at the project level. When I add the group ‘dev’to the security –> login in sql server 2005 using ssms what database access permissions do I give this group in order for them to be able to create/drop tables in the specified database.? thanks
Hi,<br />i suggest you to read below link :<br /><br /<a target="_blank" href=http://www.databasejournal.com/features/mssql/article.php/1441261>http://www.databasejournal.com/features/mssql/article.php/1441261</a><br /><br /<a target="_blank" href=http://vyaskn.tripod.com/sql_server_security_best_practices.htm>http://vyaskn.tripod.com/sql_server_security_best_practices.htm</a><br /><br /><img src=’/community/emoticons/emotion-1.gif’ alt=’:)‘ /><br />Regards<br /><br />Hemantgiri S. Goswami<br />[email protected]<br />"Humans don’t have Caliber to PASS TIME , Time it self Pass or Fail Humans" – by Hemantgiri S. Goswami<br />
IF the business logic refers to give such permission for those developers then it is upto your decision to allow such super user privilege to them. For auditing purpose you can purchase a third party tool and audit the events inorder to save the fort if any data inconsistency problem occurs. Satya SKJ
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
Hi thanks for your responses but I think I haven’t made myself clear – apologies. I have created a NT domain group called ‘developers’ and have assigned individual user accounts to this group. This group has then been created in the SQL Login under security using SSMS. This login – ‘developer’ has these database roles – db_accessadmin,db_backupoperator,db_datareader,db_datawriter,db_ddladmin,db_owner and public.
I didn’t really want to give it db_owner but in trying to find out the minimum level of privs required I started adding the extra privs and testing the create table function. So far when my developer tries to create a table in SSMS he gets this error: Property DefaultSchema is not available for Database ‘[pasom0]’. This property may not exist for this object, or may not be retrievable due to insufficient access rights. (SQLEditors) Do I have to explicitely grant the ‘create table’ permission to my group? for each database that gets created? thanks
Hi,<br />nope but db_ddladmin will do the work , here are some links for FIXED DB ROLE FYI :<br /><br /<a target="_blank" href=http://www.microsoft.com/japan/developer/library/sqldmo/dmoref_p_i_9ddl.htm>http://www.microsoft.com/japan/developer/library/sqldmo/dmoref_p_i_9ddl.htm</a><br /<a target="_blank" href=http://www.sqledit.com/sr/samples/one/Roles.html>http://www.sqledit.com/sr/samples/one/Roles.html</a><br /<a target="_blank" href=http://www.appsecinc.com/presentations/Hunting_Flaws_in_SQL_Server.pdf>http://www.appsecinc.com/presentations/Hunting_Flaws_in_SQL_Server.pdf</a><br /><br />and here are some links for error you get :<br /><br /<a target="_blank" href=http://support.microsoft.com/?kbid=910067>http://support.microsoft.com/?kbid=910067</a><br /<a target="_blank" href=http://www.sqljunkies.com/Forums/ShowPost.aspx?PostID=9921>http://www.sqljunkies.com/Forums/ShowPost.aspx?PostID=9921</a><br /><br /<a target="_blank" href=http://www.google.co.in/search?hl=en&q=This+property+may+not+exist+for+this+object%2C+or+may+not+be+retrievable+due+to+insufficient+access+rights&meta=>http://www.google.co.in/search?hl=en&q=This+property+may+not+exist+for+this+object%2C+or+may+not+be+retrievable+due+to+insufficient+access+rights&meta=</a><br /><br /><img src=’/community/emoticons/emotion-1.gif’ alt=’:)‘ /><br />Regards<br /><br />Hemantgiri S. Goswami<br />[email protected]<br />"Humans don’t have Caliber to PASS TIME , Time it self Pass or Fail Humans" – by Hemantgiri S. Goswami<br />
There seems to be a discontinuity here: Is your windows global group called ‘developer’? Otherwise it seems that you have a seperate login called ‘developer’. If you have granted db_owner, all the other database roles are irrelevant as db_owner has full control over the database. It seems that your problem is related to the schema, can you check what schemas there are and which is assigned as the default for your login/group?
]]>