asked
Removing user from db_owner
Hi everyone. I have a database user in the db_owner role that I would like to remove, for obvious reasons. The user has no object level permissions, so removing the user from db_owner will break the application. I need to grant the user only the permissions it needs. I only need a list of object names. I do not need to know the type of privilege (SELECT, INSERT, EXECUTE, etc.). Is there a way to determine ALL of the objects this user will access to?
Everyone has been helpful here. Thank you. We can probably determine most of the object this user will need by running a trace in production for a day. Also, we can QA the change to see what actually breaks. Unfortunately, the Development manager and I do not think we will be able to determine 100% of all the required permissions for this critical user in the short amount of time we have left for the next release. So, here is the solution I came up with: 1.) Remove user from db_owner. 2.) Add user to db_datareader and db_datawriter. 3.) Grant execute permissions on all stored procedures and functions for the application. This will obviously grant more permissions than needed, but at least they are out of db_owner. Please speak up if you do not agree. Again, thank you everyone. Jon
This will list all the user created objects in a database (the system objects are filtered out): SELECT name,type_desc FROM sys.objects WHERE is_ms_shipped = 0 This will not get things like database roles, just tables, stored procs etc.
Thank you, William. I thought of that...just granting the SELECT and EXECUTE permissions, even if they do not need it. That’s not ideal, but I guess it is better than leaving the user in db_owner. I guess I need a list of objects the user has recently accessed. I was wonder if there was a system view I am unaware of that logged such information.
Well you can go through the query execution statistics dmv, but that is reset when the instance is restarted or db is taken offline and back online. How about a server side trace that runs for a while? Do you have control over the entire machine? Do you have source code for software that accesses the database?
I think you're right about using a trace or dmv. I was hoping to avoid that, but it may be the best way. I do have a developer checking the source code and documentions to see if he can get me a more narrow list of objects. Thanks for your help. I will try the trace a report back. -Jon
The best way to achieve this, is to test on a test/QA environment. That way every time the user is unable to do something, they will shout. You will never know if a table or proc is needed unless you run every possible path through the app code.
