1. Mike Bayer
  2. sqlalchemy

Issues

Issue #3025 resolved

revise MSSQL default schema to take windows logins into account (?)

Brian Pantano
created an issue

I'm on SQL Server using sqlalchemy 0.9.2.

It's hard to provide a repro steps without my particular db setup.

Basically table.create will fail because "There is already an object named 'table' in the database".

It's failing because checkfirst=true is executing this SQL:

SELECT [COLUMNS_1].[TABLE_SCHEMA], [COLUMNS_1].[TABLE_NAME], [COLUMNS_1].[COLUMN_NAME], [COLUMNS_1].[IS_NULLABLE], [COLUMNS_1].[DATA_TYPE], [COLUMNS_1].[ORDINAL_POSITION], [COLUMNS_1].[CHARACTER_MAXIMUM_LENGTH], [COLUMNS_1].[NUMERIC_PRECISION], [COLUMNS_1].[NUMERIC_SCALE], [COLUMNS_1].[COLUMN_DEFAULT], [COLUMNS_1].[COLLATION_NAME] FROM [INFORMATION_SCHEMA].[COLUMNS] AS [COLUMNS_1] WHERE [COLUMNS_1].[TABLE_NAME] = CAST('table_name' AS NVARCHAR(max)) AND [COLUMNS_1].[TABLE_SCHEMA] = CAST('dbo' AS NVARCHAR(max))

is returning zero rows because the owner of the table isn't dbo, but my username.

This breaks alembic because checkfirst is used on the alembic_version table.

Comments (27)

  1. Mike Bayer repo owner

    OK are you connecting to the database using windows authentication and not SQL Server authentication? That might explain why adding a "U" there might help.

  2. Mike Bayer repo owner
    • Revised the query used to determine the current default schema name to use the database_principal_id() function in conjunction with the sys.database_principals view so that we can determine the default schema independently of the type of login in progress (e.g., SQL Server, Windows, etc). fixes #3025

    → <<cset 1fb4ad75a38c>>

  3. Mike Bayer repo owner

    OK the query I'm using now is:

            SELECT default_schema_name FROM
            sys.database_principals
            WHERE principal_id=database_principal_id()
    

    so we don't have to worry about "S" or "U" or if the same "name" appears twice, which was my concern there. I've tested the database_principal_id() function in a few places and so far it only returns "1", which is "dbo", maybe that's a SQL Server express thing, so try it out on your end to confirm this is working - thanks!

  4. André Alves

    Hi, I'm on SQL Server 2000 using sqlalchemy 0.9.7.

    After this change I'm getting:

    Error connecting to the database (ProgrammingError) ('42000', "[42000] [FreeTDS][SQL Server]'database_principal_id' is not a recognized function name. (195) (SQLExecDirectW)") '\n            SELECT default_schema_name FROM\n            sys.database_principals\n            WHERE principal_id=database_principal_id()\n        ' ()
    

    I had to revert to version 0.9.4 or add the try/except statement to the query execution like before.

    Is it possible to correct this in newer versions?

    Thank you.

  5. Mike Bayer repo owner

    someone noted this on the changeset the other day. The MSSQL dialect includes version detection so a fix would include falling back to the previous query used in _get_default_schema_name(), prior to 1fb4ad75a38ce84.

    can you try:

    diff --git a/lib/sqlalchemy/dialects/mssql/base.py b/lib/sqlalchemy/dialects/mssql/base.py
    index 3691271..aca4510 100644
    --- a/lib/sqlalchemy/dialects/mssql/base.py
    +++ b/lib/sqlalchemy/dialects/mssql/base.py
    @@ -1340,17 +1340,26 @@ class MSDialect(default.DefaultDialect):
                 self.supports_multivalues_insert = True
    
         def _get_default_schema_name(self, connection):
    -        query = sql.text("""
    -            SELECT default_schema_name FROM
    -            sys.database_principals
    -            WHERE principal_id=database_principal_id()
    -        """)
    +        if self.server_version_info <= MS_2000_VERSION:
    +            query = sql.text("""
    +                SELECT default_schema_name FROM
    +                sys.database_principals
    +                WHERE user_name IS NOT NULL AND name = user_name()
    +                AND type = 'S'
    +            """)
    +        else:
    +            query = sql.text("""
    +                SELECT default_schema_name FROM
    +                sys.database_principals
    +                WHERE principal_id=database_principal_id()
    +            """)
             default_schema_name = connection.scalar(query)
             if default_schema_name is not None:
                 return util.text_type(default_schema_name)
             else:
                 return self.schema_name
    
    +
         @_db_plus_owner
         def has_table(self, connection, tablename, dbname, owner, schema):
             columns = ischema.columns
    

    please...if it works and you can send me a pull request I can get that in ASAP.

  6. Mike Bayer repo owner

    one more try, like this please:

    diff --git a/lib/sqlalchemy/dialects/mssql/base.py b/lib/sqlalchemy/dialects/mssql/base.py
    index 3691271..d155a68 100644
    --- a/lib/sqlalchemy/dialects/mssql/base.py
    +++ b/lib/sqlalchemy/dialects/mssql/base.py
    @@ -1340,17 +1340,26 @@ class MSDialect(default.DefaultDialect):
                 self.supports_multivalues_insert = True
    
         def _get_default_schema_name(self, connection):
    -        query = sql.text("""
    -            SELECT default_schema_name FROM
    -            sys.database_principals
    -            WHERE principal_id=database_principal_id()
    -        """)
    +        if self.server_version_info < MS_2005_VERSION:
    +            query = sql.text("""
    +                SELECT default_schema_name FROM
    +                sys.database_principals
    +                WHERE user_name IS NOT NULL AND name = user_name()
    +                AND type = 'S'
    +            """)
    +        else:
    +            query = sql.text("""
    +                SELECT default_schema_name FROM
    +                sys.database_principals
    +                WHERE principal_id=database_principal_id()
    +            """)
             default_schema_name = connection.scalar(query)
             if default_schema_name is not None:
                 return util.text_type(default_schema_name)
             else:
                 return self.schema_name
    
    +
         @_db_plus_owner
         def has_table(self, connection, tablename, dbname, owner, schema):
             columns = ischema.columns
    
  7. André Alves

    Sorry for taking so long. I only got access to the environment this morning, after my last reply.

    The new solution didn't work either. I ran the query directly on the server and, as kevin_dahl wrote, the table sys.database_principals does not exist in SQL 2000.

  8. Mike Bayer repo owner

    oh, the whole table! I read that as the database_principal_id() function.

    so the way this had to have worked before was, the whole thing threw an exception which was silently caught. I'm not thrilled about that.

    What schema name should be used on SQL server 2000, should it be "dbo" in all cases? that's what it was doing before. I'd like it to actually report the real value though do you have any suggestions on that?

  9. Mike Bayer repo owner

    so then:

    diff --git a/lib/sqlalchemy/dialects/mssql/base.py b/lib/sqlalchemy/dialects/mssql/base.py
    index 4c0f6ae..5aea866 100644
    --- a/lib/sqlalchemy/dialects/mssql/base.py
    +++ b/lib/sqlalchemy/dialects/mssql/base.py
    @@ -1358,17 +1358,23 @@ class MSDialect(default.DefaultDialect):
                 self.supports_multivalues_insert = True
    
         def _get_default_schema_name(self, connection):
    -        query = sql.text("""
    -            SELECT default_schema_name FROM
    -            sys.database_principals
    -            WHERE principal_id=database_principal_id()
    -        """)
    +        if self.server_version_info < MS_2005_VERSION:
    +            query = sql.text("""
    +                select user_name()
    +            """)
    +        else:
    +            query = sql.text("""
    +                SELECT default_schema_name FROM
    +                sys.database_principals
    +                WHERE principal_id=database_principal_id()
    +            """)
             default_schema_name = connection.scalar(query)
             if default_schema_name is not None:
                 return util.text_type(default_schema_name)
             else:
                 return self.schema_name
    
    +
         @_db_plus_owner
         def has_table(self, connection, tablename, dbname, owner, schema):
             columns = ischema.columns
    

    right?

  10. Mike Bayer repo owner

    kevin_dahl : the use case is:

    1. emit "CREATE TABLE foo .." on the database, the table is created. there's no schema or owner qualifier for the table name, just "foo" as the name.

    2. later, I want to find this table in information_schema, and query: "SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='foo' AND TABLE_SCHEMA=<?OWNER?>"

    For that to work, what goes into <?OWNER?> ? "dbo", or the value of user_name() ?

  11. Mike Bayer repo owner
    • Fixed a regression from 0.9.5 caused by ticket3025 where the query used to determine "default schema" is invalid in SQL Server 2000. For SQL Server 2000 we go back to defaulting to the "schema name" parameter of the dialect, which is configurable but defaults to 'dbo'. fixes #3025

    → <<cset 6fd0bc7c6284>>

  12. Mike Bayer repo owner
    • Fixed a regression from 0.9.5 caused by ticket3025 where the query used to determine "default schema" is invalid in SQL Server 2000. For SQL Server 2000 we go back to defaulting to the "schema name" parameter of the dialect, which is configurable but defaults to 'dbo'. fixes #3025

    → <<cset c9a59575ebfd>>

  13. Log in to comment