July 4, 2008 at 5:21 am
We have a situation where we have a number of SQL Server 2000 boxes in a non-AD environment, which are used to service data to various web applications. On each database we have at a minimum two SQL logins, one for the developer so they can create and update the database, and a second more restricted login used by the website which only has db_datareader and db_datawriter permissions.
While not necessarily ideal we've set the developers as dbo's of their databases to ensure that all objects they create belong to the dbo rather than themselves, and obviously ensure that the websites can properly access those objects without having to reference the specific schema of the developer.
For the most part this works fine. The problem is that we generally configure the databases to restrict the maximum size that they can grow to, and if the developer needs more space they have to come to us for that to be approved and done. Now, since they are dbo's there is nothing to stop them from simply updating the maxsize value themselves (to date non have amazingly, but it can only be a matter of time!), which could cause issues for us.
I'm trying to find a way to either reduce the permissions level provided to the developers, without causing problems with the database objects no longer being created in the dbo schema, or to somehow restrict the rights granted to those dbo users, to prevent them from altering the database structure itself.
I had thought of simply creating a trigger on the sysfiles table, which would rollback the transaction if an insert or update operation was performed on the maxsize column by anyone not a member of the sysadmin role, but it seems you can't create a trigger on a system table.
I know that in 2005 you can use default schema's which would probably resolve this by creating a schema and then assigning all the logins to it, but that doesn't help with our 2000 boxes.
I could I suppose create a job to regularly check the status of the sysfiles table in each relevant database, and notify me if it gets changed, but that wouldn't stop the action from happening, just let me know that I need to take some kind of action after the fact.
Any suggestions would be appreciated.
July 7, 2008 at 10:11 am
Change dbo to a login other than the developers and make the developers members of db_ddladmin. This role will allow them to create objects. Instruct them to qualify object names when they create them e.g. dbo.newtable or dbo.newproc.
I don't think triggers can be created on system tables in SQL 2000. You could create a job that executes sp_changeobjectowner on objects in each database that aren't owned by dbo.
Greg
July 10, 2008 at 10:00 am
Well that's a fair suggestion though ideally I'm looking for something which is as seamless as possible from the viewpoint of the developers (Not asking for much am I! :-).
If I was starting from scratch I'd definitely go with this, but with an existing system which is used by a variety of different developers from various external companies it becomes a lot harder to control what they are doing, and of course if they did fail to follow the instructions for creating new objects things would start to break, and you can bet the clients first port of call to complain would be us rather than the developers.
So unless someone can think of something which will do it seamlessly I guess we'll just have to cope with the situation as it is, and look forward to when all the databases have been migrated eventually to 2005, since the admin workload of dealing with the work around is likely to be even greater than the risk of someone increasing the db size and us then dealing with it once it is noticed.
Thanks for trying to help though.
July 10, 2008 at 10:51 am
Yeah, I guess the trick for you is to decide which situation is most likely to happen and cause the most harm: developers increasing database size without telling you or creating objects with their own schema.
Good luck with that one!
Greg
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply