SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Mixed Authencation?
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

planetoneautomation
Yak Posting Veteran

98 Posts

Posted - 12/11/2012 :  11:39:30  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 12/11/2012 :  11:55:37  Show Profile  Reply with Quote
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
Yak Posting Veteran

98 Posts

Posted - 12/12/2012 :  09:00:34  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000