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

[Brian Pantano]

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.

[Brian Pantano]

I dug a little deeper and the fix, I think, is to change

https://bitbucket.org/zzzeek/sqlalchemy/src/99ae0dc821a1a36a6d1a966f51843f6632fc4117/lib/sqlalchemy/dialects/mssql/base.py?at=master#cl-1226

To AND (type = 'S' OR type = 'U')

Based on my reading of http://msdn.microsoft.com/en-us/library/ms187328.aspx

The workaround I'm using in the mean time is create_engine(url, schema_name='user_name').

[Mike Bayer]

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.

[Brian Pantano]

@zzzeek : yeah I'm connecting via windows authentication (which is why the OR type = 'U' fixes it)

[Mike Bayer]

hm trying to understand this: http://technet.microsoft.com/en-us/library/ms345412.aspx

DefaultDatabase

Returns the SQL Server login default database as stored in metadata or master 
if no database is specified. Returns NULL for non-SQL Server provisioned users 
(for example, Windows authenticated users).

[Mike Bayer]

• 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>>

[Mike Bayer]

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!

[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.

[Mike Bayer]

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.

[Mike Bayer]

need sql server 2000 compatibility

[André Alves]

I still get the same error.

[Mike Bayer]

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

[Kevin Dahl]

The problem is that the table sys.database_principals does not exist at all in SQL 2000. The concept of default schema in >= 2005 doesn't really exist in 2000 either, as default in 2000 is the user itself or dbo. See here:

http://msdn.microsoft.com/en-us/library/ms190387.aspx

[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.

[Mike Bayer]

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?

[Mike Bayer]

oh, my bad again just saw your link.

[Mike Bayer]

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?

[André Alves]

Right! at least for MSSQL 2000.

Thank you.

[Kevin Dahl]

I don't know the full context in which it's used in the code, but most people would probably want the default to be dbo for SQL 2000.

[Mike Bayer]

@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() ?

[Mike Bayer]

it looks like for SQL server 2000 it's been "dbo" this whole time.

[Mike Bayer]

• Fixed a regression from 0.9.5 caused by 3025 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>>

[Mike Bayer]

• Fixed a regression from 0.9.5 caused by 3025 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>>

[Mike Bayer]

well, we're still failing on this as #3810 illustrates the database_principal_id function isn't present in Azure Data Warehouse.

I've traced the history of this schema name function back to #1258. Can anyone who might happen to see this think of a reason we should not just be using schema_name() ?

[Thomas Haederle]

How does the schema_name workaound work exactly? I am connecting to a SQL Server 2008 as a member of a windows AD group. Because i login as a group member, I do not have/and cannot set a default schema. SQL Server uses my Windows login name instead. I want to create all tables/views etc in 'dbo', not in my 'username'-schema.

In Alembic I tried the following.

1) connectable = engine_from_config( config.get_section(config.config_ini_section), prefix='sqlalchemy.', poolclass=pool.NullPool, schema_name='dbo')

This should create an engine with schema_name='dbo'. However this does not work and alembic creates the 'alembic_version' table under my user name instead of dbo.

I also tried to add &schema_name=dbo to the sqlalcehmy url. Without success.

I also tried adding sqlalchemy.schema_name = "dbo" to the alembic.ini file without success.

I can create tables as 'dbo' only if I pass schema=dbo to every table definition. Tables created by alembic are always created under my user name schema.

[Mike Bayer]

@tomanizer Please report if the previous query returns a different result for you:

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

vs.

SELECT schema_name()

if your account does not have a schema name, then this should return None and we'd get "dbo". If this is not the case then that's another use case. The issue that was fixed here is that the user w/ windows login wanted that name as the schema.

However, I suspect everything works fine here and you just need to make sure to specify 'dbo' with your MetaData and/or Table definitions, this can be at the MetaData level so I'd just do it there. Please continue discussion on the mailing list at https://groups.google.com/forum/#!forum/sqlalchemy . thanks!

[Thomas Haederle]

Both queries above return "Domainname\username" which is the default behavior for user logging in via an AD group and not via user account. Table creation etc works fine as long as schema="dbo" is specified in the model.

What does not work fine is that alembic creates it's OWN tables, such as "alembic_version" under "Domainname\username". It also does not recognize any migrations in "dbo" tables. It only recognizeas migrations in "Domainname\username" -tables.

Will post a question in the forum, if this is the wrong place. Thanks for the quick answer.