May 11, 2009 at 3:54 pm
I've been all over Google and have searched this forum for a solution to this problem, but am still coming up short. I need to grant a non-administrator extra privileges so that a third-party app will run correctly.
Here is the error:
- The specified schema name "JaneDoe" either does not exist or you do not have permission to use it.Creating DeleteOptions table - Filter Server Main
We are running SQL 2005 in mixed mode. I created the user name JaneDoe, which is attached to Jane Doe's Windows account. As best as I can tell, here is what this third-party software does:
1) It creates a table in our ERP database and populates it. I think from this error that it's called "DeleteOptions".
2) It deletes data from its own SQL database and copies data from our ERP database into it.
3) It pulls the data into Excel and creates financial statements.
The application runs as it should when I run it as a domain administrator. When I attempt to run it as Jane Doe, who is a restricted user, I get the above error. So I know it's a permissions error, but I cannot, for the life of me, figure out what permissions she will need. I don't want to give her full admin rights because she is the Director of Finance and giving her full permissions on our ERP database would be a fiduciary "no-no".
Has anyone encountered this before?
May 11, 2009 at 11:35 pm
Have you tried Granting here CREATE TABLE rights on the Database?
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
May 12, 2009 at 5:36 am
Check also the default schema for the user.
[font="Verdana"]Markus Bohse[/font]
May 12, 2009 at 7:03 am
RBarryYoung (5/11/2009)
Have you tried Granting here CREATE TABLE rights on the Database?
Yeah, that was one of the first things I tried. I should be so lucky as to have the solution be that simple 🙁
May 12, 2009 at 7:06 am
MarkusB (5/12/2009)
Check also the default schema for the user.
I'm not a trained DBA, so I've more than likely done this incorrectly. Her default schema is dbo.
May 12, 2009 at 7:31 am
jblevins (5/12/2009)
MarkusB (5/12/2009)
Check also the default schema for the user.I'm not a trained DBA, so I've more than likely done this incorrectly. Her default schema is dbo.
That's the problem. Change the default schema and it should work. Or even better change your query to use the two-part name.
[font="Verdana"]Markus Bohse[/font]
May 12, 2009 at 7:31 am
Why not try to give the db_ddladmin role to that user. Maybe that user does a Truncate command or Drop command or Alter command.
-Roy
May 12, 2009 at 7:33 am
Roy Ernest (5/12/2009)
Why not try to give the db_ddladmin role to that user. Maybe that user does a Truncate command .
In that case ddl_admin would not help. Only db_owner can TRUNCATE a table.
[font="Verdana"]Markus Bohse[/font]
May 12, 2009 at 7:37 am
As per BOL
The minimum permission required is ALTER on table_name. TRUNCATE TABLE permissions default to the table owner, members of the sysadmin fixed server role, and the db_owner and db_ddladmin fixed database roles, and are not transferable. However, you can incorporate the TRUNCATE TABLE statement within a module, such as a stored procedure, and grant appropriate permissions to the module using the EXECUTE AS clause.
-Roy
May 12, 2009 at 8:37 am
MarkusB (5/12/2009)
jblevins (5/12/2009)
MarkusB (5/12/2009)
Check also the default schema for the user.I'm not a trained DBA, so I've more than likely done this incorrectly. Her default schema is dbo.
That's the problem. Change the default schema and it should work. Or even better change your query to use the two-part name.
I can change her default schema. What schema would you recommend that would give her the minimum permissions necessary to fix this issue? I would love to change the query, but it is a closed source, 3rd-party application. I can't change it 🙁
May 12, 2009 at 8:43 am
Roy Ernest (5/12/2009)
Why not try to give the db_ddladmin role to that user. Maybe that user does a Truncate command or Drop command or Alter command.
That's a possibility. I don't know what this crappy app does to the databases. I think the table is a temporary one that gets dropped when the program is done with it. If I am guessing right about the name of the table it's trying to create, then I am pretty sure it gets dropped when all is said and done.
However, this app errors out shortly after launch with that error. I wouldn't think it would be dropping the table so soon. My guess is that it can't create the table. Is there some way to see what queries have been run/attempted to run against the DB?
May 12, 2009 at 8:50 am
You can run a profiler on the the Database to find out what Queries are executed.
-Roy
May 12, 2009 at 9:08 am
jblevins (5/12/2009)
Roy Ernest (5/12/2009)
Why not try to give the db_ddladmin role to that user. Maybe that user does a Truncate command or Drop command or Alter command.That's a possibility. I don't know what this crappy app does to the databases. I think the table is a temporary one that gets dropped when the program is done with it. If I am guessing right about the name of the table it's trying to create, then I am pretty sure it gets dropped when all is said and done.
However, this app errors out shortly after launch with that error. I wouldn't think it would be dropping the table so soon. My guess is that it can't create the table. Is there some way to see what queries have been run/attempted to run against the DB?
Actually, looking at the error again, it looks more like a schema & schema permission problem than a DB one. It is apparently trying to create the table [JaneDoe].[whatever]. The first thing to ask is should it be doing that?
If the answer is yes, then check to make sure that the JaneDoe schema exists, and that she is the owner of it.
If the answer is no, then her default schema is wrong and probably should be set to dbo.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
May 12, 2009 at 9:23 am
Roy Ernest (5/12/2009)
You can run a profiler on the the Database to find out what Queries are executed.
Wow. Thank you! I had no idea the profiler existed or what it does. After hitting google for more information, I will definitely have to try running this app with the profiler running. Maybe that will give me the information that I need in order to get these permissions right. I will post back after doing this. It may be a while before I can get to it though.
May 12, 2009 at 9:30 am
Glad to be of help. Now you can debug the issue much more efficiently. There are couple of articles written here in SqlServercentral.com itself that will help you with working with Profiler. One of them is written by Brad Mcgehee. It is written in 3 parts. That should help you.
-Roy
Viewing 15 posts - 1 through 15 (of 21 total)
You must be logged in to reply to this topic. Login to reply