May 22, 2002 at 5:35 am
I have just noticed that a few databases have been created in our new SQL server database. These databases table are owned by a user other than dbo. I vaguely recollect from a course of mine that this might pose a security risk and it is not generally recommended. Can you enlighten me on this please? I need to warn our Technical Infracstructure guy's about this.
May 22, 2002 at 5:50 am
The biggest issue with doing this is it breaks the ownership chains and you will most likely have to give direct access to the tables where a stored procedure or view helps protect from this need when ownership chain is dbo, and thus your data is more secure. The other thing is it can make finding an issue more complicated than needs to be.
If you want to change all the items to dbo you can check out http://www.sqlservercentral.com/faq/viewfaqanswer.asp?categoryid=2&faqid=163 for some help to quickly fix tables. But you will have to use sp_changeobjectowner on each object, plus you will need to check internal references in Views and SPs that reference the original owner.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
May 22, 2002 at 6:17 am
Also have to look at fixing it moving forward. If you have a user(s) that you want to be able to create tables, I usually alias them as DBO. On the other hand, you may not want to let them create their objects - with a couple exceptions I don't.
I don't see any extra risk associated with the ownership, just more complexity as mentioned by my planetary colleague.
Andy
May 22, 2002 at 1:23 pm
Good advice above. Keep in mind when explaining that the KISS principle also helps maintain a stable environment. This tends to violate that.
Steve Jones
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply