MS SQL Security, DBO vs User objects
Posted on 2007-08-09
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.