May 8, 2008 at 5:57 am
This is my first Post here. i´m from germany, so please sorry for my english!
In my company i´m the MS SQL Admin and my strategy of assigning rights is restrictive.
So i doesnt assign dbowner to a customer, because the dbo can delete the own database, or even worse grow the own database so that Disk space runs out. This would also effect other databases.
Now my Boss wants an explanation why i wouldn´t give dbowner to others. Nearly everly application which uses a MS SQL Database "needs" dbo-rights. But i doesn´t understand why. Yes, with dbo-rights all does work fine, but it should also work wihout these rights. I think that the majority is simply too lazy to split up which rights and roles are really needed, because with dbo all works fine. But what is with security and availability?
Sadly, my Boss doesn´t have my opinion. He says, that this what i said is not true, and there is no danger.
So i have to write an explanation. It might be that i have to give dbowner though, but with the explanation i can shift the responsibility away from me. I hope so.
So, can you help me finding reasons not to give away dbowner rights?
Or is it ok to give away dbo-rights and i have to monitor if an user with dbo rights alters the database?
I hope you are not too scared of my bad english!
Thank for your help in advance!
Greetings
Mario
May 8, 2008 at 6:12 am
As far as I am concerned there is no reason for anyone to be a member of the db_owner role in a production database. The DBA should create roles and assign appropriate rights to the roles and then assign users to the appropriate roles.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
May 8, 2008 at 11:25 am
I have always created a user for the vendor application to use that has DBO rights. Not from a standpoint of laziness but from the standpoint that the vendor application needed the ability to modify schemas, stored procs, etc. as well as modifying the data. For an actual human user, I would not be inclined to grant dbo rights but if the application is maintaining the database, it does need the rights. I did force my vendors at my previous employer to allow me to store the username/password in the connection file encrypted so they would not have access to login themselves with those rights.
Allow me to share my experience where I'm at now. I just started a new job in March. One of my first responsibilities is to migrate from 2000 to 2005. In auditing what is in our 2000 server, I found multiple users with SYSADMIN rights! The people I'm working with don't know anything about SQL Server so when a vendor asked for the rights, they didn't know how to respond.
Of course, I won't be granting those rights on the new server. I will have a challenge with one of our vendors, though. Since they have had sysadmin rights, they have written their application with that assumption. I did have a conversation with them and understand what they are trying to accomplish and why they thought they needed those rights. It took some doing but I was able to convince them that they don't need all of those rights since I'm here now to take care of the administration that they were having to do from an application standpoint.
I think the most important thing to look at when talking about database rights is to determine what needs to be accomplished when that user is logged in. A user the application uses to connect to the database may very well need dbo rights, especially when dealing with a vendor application. I do err on the side of caution and run as restrictive as possible when granting humans access to the database. Since most of our users do not directly connect to the database, I have a relatively easy job. I just have to clean up the holes left from not having a DBA who can appropriately manage the rights.
May 9, 2008 at 12:01 pm
I have a big issue with granting any login db_owner and especially sysadmin. I have recently gone through a similar cleanup where users had this level of access. I don't see a need to grant users anything greater than datereader/datawriter/ddladmin if there is a competent DBA in-house. The DBA should be taking care of schema changes, database development needs, user management, etc... It's tough getting management to agree with locking down customers. Management/Sales is all about pleasing the customer and this is why there will always be a struggle with IT. Good luck.
May 9, 2008 at 12:38 pm
In the production environment, No one gets Db_owner rights. No one. Now in a development environment, I tend to be more lax as I don't want to be the slowdown when there are about 20 developers and one dba.
Marvin Dillard
Senior Consultant
Claraview Inc
May 15, 2008 at 1:35 am
Thank you very much for your advices!
I hope I can persuade my Boss to not giving away db_owner.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply