[sqlalchemy] mapper for ms sql server

[Tan Yi]

Hello. Recently I have been working on this environment:
linux + pyodbc + sqlalchemy 5.8 + ms sql server.
In the program, I try to build a mapper from a db table to my python
class, then I instatiate the class, and try to use session.add() to
insert record to database.
However, I need to have "alter any schema" permission in order to make
it through,
If I do not have this permission, I will get
File "C:\Python25\lib\site-packages\sqlalchemy\databases\mssql.py",
line 954, in pre_exec

self.dialect.identifier_preparer.format_table(self.compiled.statement.table))
ProgrammingError: ('42000', '[42000] [Microsoft][ODBC SQL Server
Driver][SQL Server]Cannot find the object "Sources" because it does
not exist or you do not have permissions. (1088) (SQLExecDirectW)')
exception.

wondering is If there is any other way to work around this issue,
assuming no "alter any schema" permission given.

Thanks!
---Tony

--
You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
To post to this group, send email to sqlal...@googlegroups.com.
To unsubscribe from this group, send email to sqlalchemy+...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.

[Michael Bayer]

Tan Yi wrote:
> Hello. Recently I have been working on this environment:
> linux + pyodbc + sqlalchemy 5.8 + ms sql server.
> In the program, I try to build a mapper from a db table to my python
> class, then I instatiate the class, and try to use session.add() to
> insert record to database.
> However, I need to have "alter any schema" permission in order to make
> it through,
> If I do not have this permission, I will get
> File "C:\Python25\lib\site-packages\sqlalchemy\databases\mssql.py",
> line 954, in pre_exec
>
> self.dialect.identifier_preparer.format_table(self.compiled.statement.table))
> ProgrammingError: ('42000', '[42000] [Microsoft][ODBC SQL Server
> Driver][SQL Server]Cannot find the object "Sources" because it does
> not exist or you do not have permissions. (1088) (SQLExecDirectW)')
> exception.
>
> wondering is If there is any other way to work around this issue,
> assuming no "alter any schema" permission given.

I could not begin to imagine why an INSERT would require "ALTER SCHEMA" as
a permission.

[desmaj]

I'd be curious to see if it's really a lack of ALTER SCHEMA that's the
culprit. Are you sure that the only thing you are varying to make it
work is ALTER SCHEMA?

This looks like a lack of access to the 'Sources' table, which can
happen if the user that creates the table is missing certain
properties. I think that if the account that created the table has the
SA (Server Administrator?) role on the server then the table should be
accessible to everyone. dbo may also be required. If an unprivileged
user creates a table, I believe that, by default, it is inacessible to
other unprivilieged users.

So: verify that the table was created by SA (possibly dbo).

Disclaimer - this is all off the top of my head. I got this from the
docs, but I have a tough time getting my head around msdn navigation
so I'm not sure I could point you to where I found this information.

[Tan Yi]

I think what desmaj said makes a good point. Actually the Source table/
mapper indeed was created by someone else, and I just used Attach
database to attach the mdf file and log file to the server.
But how am I going to deal with this though? should I go ahead and use
SA's account and create a copy of this database?
Thanks...