Aside from executing XP_CmdShell, which I have disabled in my SQL 2005 installation, what could a malicious user who gains DBO rights to my database do:
I'm assessing the worst-case security risk of someone obtaining DBO to justify running a "least-privileged" user account in an application. Some allege that since we're not working with "confidential data" that the impact of someone gaining DBO is minimal.
he can run any XP_ sproc so it can mess up the registry and mess up your whole server for one thing. drop/change tables, etc...
Lots of info online about SQL injection. Limiting the privs of the user you connect to SQL with is one very important defesse.
http://en.wikipedia.org/wiki/SQL_injection
Here's a simple, frightening example I ran into a couple of years ago. Website generates SQL stateemnts on the fly using URLs and runs them. I was able to guess that URLs like
www.blah.com/.../load.aspx?itemid=1
produced SQL like this
SELECT * FROM items where id=1
so I sent this url:
www.blah.com/.../load.aspx?itemid=1;drop table items
bang.
At the very least, if the DB connection was not dbo this would have failed.
dbo is Database Owner - which may or may not be sa. As dbo, you basically have free reign over the owned database - obviously, this could lead to changed permissions, dropped tables, etc.
As sa, you have free reign over the server. This includes installing new extended procedures (or .NET assemblies if you have CLR enabled), as well as everything dbo can do (for all databases). And, you can also enable xp_cmdshell again.
To put it bluntly, if he owns your database, he pwns your database. That is to say, you can kiss your data good-bye.
That is to say, you can kiss your data good-bye.
But you have backups on disk and on tape, so you are golden!
Yeah. The typical defense is "well, at least he can't mess up anything outside of his database" (xp_CmdShell is not enabled). Me, I'm not comfortable having a mad-dog rapist in a locked closet in my house. But that analogy isn't a great sell to management.