SQL Server Performance Forum – Threads Archive
Help a newbieI am working on my first SQL database project. I just converted my Access .mdb to an .adp. Most thing are working fine. There is one particular issue I have not been able to find anything on and I am not even sure how to look it up. When any user except myself opens the .adp file the tables and querys are renamed with a (dbo) added to each items name. This is kind of an issue since the forms and reports don’t work with the changed names. Could this be because I have the users permissions set to db_owner so that they can access the tables and queries? This is a SQL 2000 database with Windows Authentication and an Access 2003 .adp file for the front end. TIA for any help.
From BOL The dbo is a user that has implied permissions to perform all activities in the database. Any member of the sysadmin fixed server role who uses a database is mapped to the special user inside each database called dbo. Also, any object created by any member of the sysadmin fixed server role belongs to dbo automatically. For example, if user Andrew is a member of the sysadmin fixed server role and creates a table T1, T1 belongs to dbo and is qualified as dbo.T1, not as Andrew.T1. Conversely, if Andrew is not a member of the sysadmin fixed server role but is a member only of the db_owner fixed database role and creates a table T1, T1 belongs to Andrew and is qualified as Andrew.T1. The table belongs to Andrew because he did not qualify the table as dbo.T1. The dbo user cannot be deleted and is always present in every database. Only objects created by members of the sysadmin fixed server role (or by the dbo user) belong to dbo. Objects created by any other user who is not also a member of the sysadmin fixed server role (including members of the db_owner fixed database role): Belong to the user creating the object, not dbo.
Are qualified with the name of the user who created the object.
All of the database objects were made by sysadmin and are owned by dbo. I decided to make my user a member of the sysadmin and the issue went away. Now I just need to figure out what permissions I need to set so she can work but not be a memeber of sysadmin. I know about the naming scheme of dbo.T1. That is not actually what is happening though. i.e. I have a table named "tblEmployeeHours". My user sees the table as "tblEmployeeHours (dbo)". There is a space after the name and (dbo). The report linked to that table does not work because it is looking for "tblEmployeeHours" not "tblEmployeeHours (dbo)".