SQL Server 2005 Security

Question

Here is the scenario. I have a SQL Server 2005 production database/server. It currently has developers and supporters who can connect to it. I need to create a security module that gives developers read-only access to all areas of the database. This means that a developer should be able to view all objects as well as scheduled activities/jobs only.

Is it possible to enable security in this way and if so can I be gently guided on how to achieve this. I am learning to be a DBA and creating snapshots of the databases are not an option.

Thank you all in advance.

Answer 1

There is permission to every object. Create a stored procedure that grant each gruop the exact permission you need on the objects you need to protect.

Answer 2

I'm not quite sure I follow where this "security module" will be in the architecture. Anyhow, here's one possibility that secures it from the database end.

I'm going to assume you already have users created.

Create a new role (yourdb > security > roles > new database role), say "ReadOnlyDevelopers". Make the owner dbo or whatever makes sense. Do not select any schemas to be owned by the role. Populate the "Role Members" with your developers.

Next, open the properties page on your database. Go to the permissions page. Click Add... and add the new role. Under the permissions grid at the bottom, Grant SELECT to the role.

Now assuming your developers already belong to some other role, you'll need to go into the user properties and under Database Role Membership restrict them to just the new role. At this point they should be able to just read

I'm guessing that I'm missing a detail or two (the role may need to be grated a few additional rights to "see" the database, alter passwords, etc.) but I can't get to that level of detail without setting up the entire scenario. Hopefully this pushes you in the right direction.