Manage Learn to apply best practices and optimize your operations.

Converting logins after an upgrade

SQL Server 2005 expert Adam Machanic clarifies some misunderstandings about login conversion after an upgrade.

I have a question regarding logins while upgrading to SQL 2005. It looks like SQL Server 2005 treats each user as it's own schema and therefore you will have as many schemas as the number of users in a database.

How should I approach this in SQL Server 2005?

Also, what kind of problems should I expect while doing an in-place upgrade, especially in the context of converting or upgrading logins?

Actually, you have the situation somewhat reversed. In SQL Server 7 and 2000, when you create an object owned by a user other than DBO, you have to qualify the object by owner name when accessing it, unless you are logged in as that user.

In SQL Server 2005, objects are no longer qualified by owner. Instead, they're qualified by schema name. This is something I covered in a recent SearchSQLServer.com tip on SQL Server 2005 permissions. In essence, this will result in less confusion regarding object qualification; you will be able to organize objects logically, rather than by who owns them.

As for server logins, I don't see a lot of potential issues. The same semantics apply in terms of what logins are to SQL Server and how they work. One difference you might want to be aware of is SQL Server 2005's ability to enforce password policies, which may mean that some of your users will have to change their passwords after your upgrade.

Dig Deeper on Microsoft SQL Server 2005

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.