how to set permission for a database

hi,

I need to do a security setup in SQL server 2000. the scenario is as follows:

In a team, only one person(Team Leader) should access the particular DB (through Enterprise Manager and rest of the persons in the Team(developers) should access through code.

How to do this.

Thanks and Regards

Sridhar, not sure what you mean by "through code"... executing code in an app? or maintaining proc/trigger code on a database? You're trying to restirct WHAT groups can do, not necesarily thru what tool? (because if a developer has permission to update tables, they can get in thru EM)... In SQL 2005 SP2, you can put a SERVER TRIGGER on LOGONS and probably not allow certain logons in thru certain application names that way.. What type of access are you talking about? Bruce|||

In SQL server 2005 we introduced a new feature that hopefully will help to solve your scenario: digital signatures in SQL Modules.

The general idea is to allow certain permissions only when the call comes directly from a signed module (SP, user defined function, etc.). I strongly recommend reading the following resources:

· BOL

o Understanding Execution Context:http://msdn2.microsoft.com/en-us/library/ms187096.aspx

o Module Signing: http://msdn2.microsoft.com/en-us/library/ms345102.aspx

o ADD SIGNATURE: http://msdn2.microsoft.com/en-us/library/ms181700.aspx

· Laurentiu Cristofor’s Blog:

o http://blogs.msdn.com/lcris/archive/2005/06/15/429631.aspx

o http://blogs.msdn.com/lcris/archive/tags/SQL+Server+-+cryptography/default.aspx?p=2

· Raul Garcia’s blog:

o http://blogs.msdn.com/raulga/archive/tags/SQL+Server+Signatures/default.aspx

Feel free to ask us any question or feedback on this topic. We will be glad to help.

Thanks,

-Raul Garcia

SDE/T

SQL Server Engine

|||

hi bruce,

1. In my setup there are some developers for a project accessing the database through th EM and once the same once the same project implemented in production level, it should not get accessed through the EM (either they may only view the tables not edit/delete). how to do this?

2. What permission should be given to database so that throgh the application it can be edited/deleted/updated and not through the EM.

|||

I strongly recommend reading the following thread. Here we discuss in detail about application security, and from your last post, I think it is something similar to what you are asking for:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=775364&SiteID=1

Please let us know if this information was useful.

Thanks,

-Raul Garcia

SDE/T

SQL Server Engine