July 31, 2002 at 7:36 am
I've been developing an application that has an Access 2000 ADP front end and a SQL Server 7 back end. (This is my first SQL Server experience.) It's just gotten to a point where it can be tested. Everything works fine if I am logged in, but not when someone else is logged in. The error I get is "CREATE TABLE permission denied, database 'MTDev', owner 'dbo." I've even tried giving the users all rights to all of the objects. Am I missing something? It's probably (hopefully) a simple answer.
Here's what's supposed to happen: The user is importing records from an Excel file into a temporary SQL table. I created the temp tables with my logon, so I am the dbo. For some reason, it's trying to create a brand new table during the import only when someone else is logged in. I want it to go into the table I created. Any suggestions??
July 31, 2002 at 7:42 am
Have you given "CREATE TABLE" permissions?
If not add the user into the database. In EM, right click on the database and select properties. Choose the permissions tab and *grant* CREATE TABLES.
Better still, put your users into a Group within the Database and Grant permissions to the Group...thats if you want more than one person have these permissions!
Clive Strong
July 31, 2002 at 7:50 am
I would recommend using a role even if its only one person. That person may leave eventually and be replaced, need to authorize a second person, etc. With a role all the thinking is done, just add the user to the role!
Andy
July 31, 2002 at 8:02 am
I do have roles set up. I gave the user's role all possible permissions on all objects. Also, I don't want the process to create a new table. I want the process to import the records into the table I have created. So, giving them the rights to create the table will not solve my problem - will it?
I tried giving the user dbowner rights just to see what would happen. It created a brand new table named exactly the same as my table. My table looked like "tableX (dbo)" and her table looked like "tableX (cathy)". To me that means that she doesn't have rights to my table. But she has full complete rights to all tables - and all objects for that matter. Any other suggestions? Thanks.
July 31, 2002 at 8:39 am
Hi,
Curious indeed! How is the data being imported from Excel into SQL Server? Are you using a DTS or are you using code to read from Excel and populate your table(s)?
How did you originally create the temp table? EM? A script in QA? In your frontend code? A DTS?
Clive Strong
July 31, 2002 at 9:52 am
There is a one-line command in Access 2000 called TransferSpreadsheet. You give it the parameters - spreadsheet location, and table name to insert to. When I created the table, I was in the Access 2000 ADP front end. You can do almost everything you can do in EM.
September 28, 2002 at 4:24 am
I've actually been working on this problem myself. I have NT security using groups assigned to roles. Access 2000 is used to import text files. When I'm logged in everything works fine. When the user logs in it changes the table names to table (username) even though they have dbo permissions. VBA can't find the table name specified in the code. I only have 10 tables that the users imports text files into so it really isn't worth writing an entire front-end to do this. Microsoft projects (ADP) seems to have a lot of problems.
September 29, 2002 at 8:37 pm
quote:
I've actually been working on this problem myself. I have NT security using groups assigned to roles. Access 2000 is used to import text files. When I'm logged in everything works fine. When the user logs in it changes the table names to table (username) even though they have dbo permissions. VBA can't find the table name specified in the code. I only have 10 tables that the users imports text files into so it really isn't worth writing an entire front-end to do this. Microsoft projects (ADP) seems to have a lot of problems.
September 29, 2002 at 8:52 pm
Sorry for the blank reply. What I ment to say was that it looks like you are running in mixed mode and have granted specific rights to the role. I think you need to add the role to the admin role in EM. If the role the users are in is not in admin role, then tables, sprocs and such will not be dbo.table, but instead username.table.
October 1, 2002 at 1:21 pm
I think you may be hitting some snags due to the TransferSpreadsheet Action in Access.
May I suggest you uncomplicate things by linking both the spreadsheet file and the SQL Server table at run time and transfer data via an Access Append Query?
I think you'll find development, administration and support will be significantly easier under that model.
October 1, 2002 at 2:53 pm
Easy solution: include your users in 'db_owner' standard database role.
Could try with 'ddl_admin' insted.
Of course 'db_datareader', 'db_datawriter' is a must for this users.
ps: And also could avoid creating tables 'not so temporals' on the fly...
quote:
I've been developing an application that has an Access 2000 ADP front end and a SQL Server 7 back end. (This is my first SQL Server experience.) It's just gotten to a point where it can be tested. Everything works fine if I am logged in, but not when someone else is logged in. The error I get is "CREATE TABLE permission denied, database 'MTDev', owner 'dbo." I've even tried giving the users all rights to all of the objects. Am I missing something? It's probably (hopefully) a simple answer.Here's what's supposed to happen: The user is importing records from an Excel file into a temporary SQL table. I created the temp tables with my logon, so I am the dbo. For some reason, it's trying to create a brand new table during the import only when someone else is logged in. I want it to go into the table I created. Any suggestions??
October 3, 2002 at 11:35 am
I'm running in WINDOWS auth mode and the NT groups are part part of the database owner role. It actual says Tablename (dbo) when someone from the NT group logs in.
October 3, 2002 at 11:42 am
When users create tables, and are members of the db_owner role, then all tables they create, without specifically specifying the owner, will be created with DBO as the owner.
Gregory Larsen, DBA
If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples
Gregory A. Larsen, MVP
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply