SQL Server Performance Forum – Threads Archive

DB_Owner Security in SQLServer 2000

I have a application that has not been changed for 10 months, 99% percent of the application still can save ok to the SQL Server database. However, yesterday one part of the application could not save to the database anymore. The user just would see the hour glass, the database would not show any locks or blocks. The database or application has not been changed. However, we had one user that had DB_owner rights to the database, she was the only one that could work. I changed our security user group to have DB_owner rights and now everyone works ok. Ok, the question. Why would SQL Server now require this new setting on the database role. What happened. Thanks for any ideas…. Also, what does DB_owner add.
11/21/05 3:00pm – I have a online back off of this database, it did not have DB_owner checked and it is working ok. I now changed the live database back to the way it was, without the db_owner and now it is also working ok. Last night, neither the backup copy or the live database would work until db_owner was checked, now they do. Any ideas, what cleared it up overnight? Weird

Any object in SQL Server has security settings that must be met when access for (reading, updating, deleting etc). You can specifically grant or revoke permissions to individuals or to groups. Thus, if you create a new user you can say "This person can READ but not update the data on this OBJECT." From what you’ve indicated it sounds like 1 of 3 things did happen. 1. The security group used to have the DB_Owner rights previously, and someone changed them while "playing around with the system." If you are questioning what the "DB_Owner" rights would be, then odds are someone else might have seen that permission assigned to that group and removed it. (One simple change could have implemented this and could have been a misunderstanding.) 2. There used to be specific permissions assigned to the security group for individual objects, that were then revoked. (This would have required a little more work to make happen and not likely on accident.) 3. The objects in your database were previously set to be owned by that security group. So they had all the rights needed, and the lady who had the DB_Owner rights also had the rights. Someone may have changed the owner of the tables to be the DBO for the database, which then removed the default privelages that group had to the tables. (One simple change could have implemented this and could have been a misunderstanding.) The DB_Owner permission simply says "pretend that this group owns the database, and grant them all permissions to do anything with any of the objects in the database." If they have that permission then it will supercede all individual objects by the objects themselves.

PROFILER or server side trace will also help to assess what is happening on the SQL server, change the database owner to SA in order to avoid any network related issues associated with the user logins. I feel Druer explained the scenario effectively for our better understanding. Satya SKJ
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

Who is the database owner currently?
Check the user privileges and any policies who was previous owner of thsi database Satya SKJ
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

]]>