June 23, 2010 at 12:09 pm
I am an accidental DBA for our company. We have several report/tsql developers, with average tsql skills. I trust them with tsql scripts, but not with the administration functions. The company's approach has been to pass out the server admin rights like candy. I am not for that and am trying to tighten things up. I want them to be able to install new objects and run scripts, but I want to remove them from admin. Our existing servers are a mess.
Now we recently set up a new SQL08 cluster on 2 nodes. I have convinced the sys admin that we can better manage these boxes. We have agreed that there would only be two admins, myself and him, and all access would be via AD groups.
I have also proposed that the developers get assigned another AD login that I could assign higher rights to. The high level account would be for DDL changes and would be used on a limited basis. Their low level account would only have read rights on the server and they would mainly use that for day to day work.
So I believe I have some good ideas here, but getting the security setup right the first time is important to convincing everyone that security can be implemented without hindering productivity or becoming a nuisance.
Basic info:
Developers will develop objects in more then one database within the dbo schema. They all should be able to access everything within the database, but should not be able to mess with database or sql instance security. If it is a server level function, I don't want them to have it. They will need the ability to install and start sql agent jobs. The AD Group name for them will be something like myserver_dev for the high level accounts (The low level read accounts I have taken care of.)
Please share your approach to this situation, or practices you have implemented that worked out well. Also, if you have links to other sites that would be fine as well.
Thanks in advance
Will
July 21, 2010 at 2:10 pm
I have a very similar problem. We have many developpers that are coding stored procedures to be used in Reporting Services. I want them to be able to create/alter/drop procedures but not create/alter/drop tables.
How would I go about implementing security for that?
___________________________________
I love you but you're standing on my foot.
July 21, 2010 at 2:19 pm
To answer my own question, a ddl trigger based on object type might do the trick.
July 21, 2010 at 2:30 pm
You could use schemas. Put the tables in one schema and give SELECT permission to the developers. Give them another schema on which they have CREATE permission. That is where they would put their stored procedures.
July 21, 2010 at 2:38 pm
Thanks, but they have to use the dbo.
In a past life I had the report procs in a separate database and used the rpt schema, but that won't work here, its too late to implement that.
How is this done in a real company?
July 21, 2010 at 2:45 pm
Unfortunately, it's usually done by not giving your developers CONTROL permission on the dbo schema in the first place. When best practices aren't followed, it usually takes more work to fix the problem that it would have taken to implement the best practice in the first place.
There are still a few avenues you could try. You could try explicitly denying permissions to the tables themselves. You could move all the stored procedures and provide synonyms (I'm guess there are a lot though, I know as a single developer I have hundreds). You could draw a line in the sand and move over stored procedures gradually, as changes are required. Or a DDL trigger could work as well, but I think that is a little less than optimal. I'm sure there are other ways too, it might just take a little brainstorming.
Generally, I think the gradual change is the best. These things happen naturally. Naming conventions is the best example I can think of. When you start coding something, you have a vague idea what naming convention you want to use, but until you have 100+ objects, you really don't know what's going to work best. At some point, you'll end up changing (or formalizing) naming conventions with a heap of objects that don't fit it. You don't change all the objects at once. As they each of the older objects is touched, then you move it over to the new convention. This kind of thing happens with best practices all the time. Best practices are dictated from the gods; they are discovered organicly when poorer practices are observed.
July 21, 2010 at 4:04 pm
Thanks for your help. I think I will call this post resolved, I think the question would make a good book.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply