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?
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.
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.