MS SQL Security, DBO vs User objects

We've been having a debate here at work about security in MS SQL.  

One of the managers here is asking us to modify the database and application to drop the objects (tables, stored procs) created by the database admin (sa) and prefixed DBO and to re-create the same objects using the user created to access them (so they are prefixed with the user.

We've been arguing on this as my view on this is that not only there is no gain in security, this causes actually a breech, because this user can actually drop objects.

I'm not a SQL Guru, but here is what I would do:

Create a user that would be DBO of the database.  This user creates objects, sets permissions.
Create a user that accesses the DBO (datareader, datawriter).  Can execute stored procs and use views.

Tell me what you think, if it's worth arguing with this manager or let go and accept what the manager wants, modify the app code and DB stored procs.

Thanks
Marc
csbintraAsked:
Who is Participating?
 
Scott PletcherConnect With a Mentor Senior DBACommented:
One big disadvantage of having a custom owner is that the owner must be specified by everyone who is not the specific owner using the object.  Also, you will have to do multiple GRANTs of authority, since every time the owner changes, the ownership chain is broken, and SQL starts checking all permissions again.

From an administrative and maintenance standpoint, it's much easier to use 'dbo' as the owner.

>>  to drop the objects (tables, stored procs) created by the database admin (sa) and prefixed DBO and to re-create the same objects using the user created to access them (so they are prefixed with the user. <<
You can use sp_changeobjectowner instead, you don't have to drop and re-create the objects.
0
 
Jens FiedererConnect With a Mentor Test Developer/ValidatorCommented:
It really depends on what ELSE is in the database.

If your team is the only one that maintains that database, dbo access for one or more of you might be acceptable.  

It the database contains (or WILL contain) the work of OTHER teams to which you are not supposed to have access, allowing your team to have DBO is obviously a problem.
0
 
csbintraAuthor Commented:
I'm sorry, I might not be very clear on the issue.

Actually, the issue or question is:  Is it safer to use objects created by the application user accessing the db (user.tblTable), or to use objects created by DBO (dbo.tblTable) and accessed by an app user with data_reader/data_writer permissions...

Thanks in advance,
Marc
0
 
Jens FiedererConnect With a Mentor Test Developer/ValidatorCommented:
There is nothing generally unsafe about objects created by DBO accessed by an app user with data_reader/data_writer permissions...

If there is any security-related issue that suggest a preference for user created objects, it is MUCH more likely to relate to table maintenance issues (and who needs to get DBO) than it is to relate to table access during the normal running of the application.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.