Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Mixed Authencation?

Author  Topic 

planetoneautomation
Posting Yak Master

105 Posts

Posted - 2012-12-11 : 11:39:30
I am getting the message:

"Some tables were created with SQL Server Authentication and some with Windows Authentication"

... while running a program that upgrades some particular HP software which uses SQL Server 2008 R2. How does the creation of a table in SQL Server had anything to do with how authentication was done?

1. How to tell if a table in a database was created using SQL Auth versus Windows Auth
2. How to change how a table "thinks" it was created in terms of authentication

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-12-11 : 11:55:37
That message does not seem like a message from SQL Server.

In any case, I don't know of a way to identify who created a table directly. Couple of thoughts:

a) Use SELECT * FROM INFORMATION_SCHEMA.[TABLES] and see if there are TABLE_SCHEMA's that correspond to default schemas of windows authentication users, which may give an indication.

b) You can right click on the database in Object Explorer and select Reports -> Standard Reports -> Schema Change History to see if that gives any information.
Go to Top of Page

planetoneautomation
Posting Yak Master

105 Posts

Posted - 2012-12-12 : 09:00:34
Thanks. The issue is resolved.

Turned out, the HP application (HP Application Lifecycle Management, otherwise known as ALM or previously known as Quality Center) was running a process to upgrade from a previous version where the DB was moved from SQL 2005 to SQL 2008.

The tables in the source system are owned by a user 'td' and in the target system, they need to be owned by user 'dbo'. I was aware of this and had run ALTER TABLE to change owners but a few tables were missed (my bad).

The message "Some tables were created with SQL Server Authentication and some with Windows Authentication" is a message from the HP software and it means some tables are still owned by 'td' rather than 'dbo'.

An ALTER TABLE to change the owner to 'dbo' for the tables that remained owned by 'td' resolved the issue.
Go to Top of Page
   

- Advertisement -