Much ado about logins and SIDs

Some time back I posted about Logins and SIDs and I’ve had a number of people ask me for examples. So I’ve updated the post here.

A very common issue that's raised in the newsgroups relates to SQL Server logins that need to be moved between servers. When you recreate a SQL Server login (ie: not a Windows one), by default you get a new security ID (SID), even though you have the same user name and password.

The problem then comes when you restore a database from another server. You can't access it. If you try to create the user entry in the database, it says it already exists and fails. But if you try to list the users in the database, it also doesn't show it.

Let’s try an example: First I’ll create a database and a login and add the user to the database:

CREATE DATABASE LoginTest;

GO

CREATE LOGIN GregTest WITH PASSWORD = 'P@ssw0rd';

GO

USE LoginTest;

GO

CREATE USER GregTest FROM LOGIN GregTest;

GO

USE master;

GO

 

Next we’ll detach the database and drop and recreate the login:

EXEC sp_detach_db 'LoginTest';

GO

DROP LOGIN GregTest;

GO

CREATE LOGIN GregTest WITH PASSWORD = 'P@ssw0rd';

GO

 

If we reattach the database, we now have the situation where the new login has a different SID to the one that the user has in the database, even though the user has the same name. This is very much like what happens when you restore a database on another server and recreate the login there:

CREATE DATABASE LoginTest

ON (FILENAME = 'C:\SQLData\Data\LoginTest.mdf')

FOR ATTACH;

GO

 

If we try to use the login to access the database, it won’t work. If we try to create the login, it won’t work either:

USE LoginTest;

GO

CREATE USER GregTest FOR LOGIN GregTest;

GO

 

Msg 15023, Level 16, State 1, Line 1

User, group, or role 'GregTest' already exists in the current database.

 

The standard answer for this has been to use sp_change_users_login. It has an option to list any mismatched logins and database users ie: those with the same names but different SIDs.

 

EXEC sp_change_users_login 'Report';

GO

 

UserName    UserSID

———————————————-

GregTest    0x5D5F9089AFE1D4428106DE1B52BE0DFC

It then has an option to "fix" it. The way it fixes it is to update the SID in the database user to match the login:

EXEC sp_change_users_login 'Update_One', 'GregTest', 'GregTest';

GO

 

In Service Pack 2 of SQL Server 2005, new syntax was introduced to deal with this:

ALTER USER GregTest WITH LOGIN = GregTest;

GO

 

The problem I see with this all is that it still just temporarily fixes the problem or at worst, propagates it to other servers. It's not the database SID that needs fixing, it's the Login's SID. If the Login's SID was correct, there wouldn't be a problem with copying the databases around. The most common scenario I see is the following:

1. A database is restored from another server (or a reinstalled server).

2. The logins that use the database need to be recreated.

I've gotten around this problem in the past by specifying the SID value when creating the login in TSQL. It is an optional parameter. If you provide the same value as on the other server, you don't have the problem. For example, instead of executing sp_change_users_login or ALTER USER above, we could have done the following:

USE LoginTest;

GO

SELECT sid FROM sysusers WHERE name = 'GregTest';

GO

 

sid

———————————–

0x5D5F9089AFE1D4428106DE1B52BE0DFC

 

(1 row(s) affected)

What we could then have done was:

CREATE LOGIN GregTest WITH PASSWORD = 'P@ssw0rd',

  SID = 0x5D5F9089AFE1D4428106DE1B52BE0DFC;

GO

 

The upside of this is that it’s a permanent fix. Next time you restore the database, you won’t have to fix it again.

Because this is such a common issue, I’d really like to see it directly supported in T-SQL, such as:

CREATE LOGIN GregTest WITH PASSWORD = 'P@ssw0rd',

  SID FROM DATABASE LoginTest;

GO

 

This would avoid the problem in the first place and then give you a database you could copy around as needed. If you think that sounds interesting, vote for it here:

 https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=269442

Canberra SQL Group Next Week

Just had our Canberra mate Jeff Wharton confirm that he's organised a SQL user group meeting while I'm there next week. I'll be doing the session "Answering the queries your users really want to ask" which covers full-text indexes in SQL Server 2008. I find three categories of people in these sessions:

* those that have never used full-text indexing in SQL Server

* those that have old pain associated with trying to use it

* those that are using it and are interested in what's new in SQL Server 2008

Whichever category you fall into, we'll cover off why it should be part of your arsenal from SQL Server 2008 onwards. If you'll be in Canberra, we'd love to see you there. Meeting is Tuesday 10th February at Excom in the city, 6pm start (food from 5:30) and we'll be done about 7pm.

SQL Server 2008 System Views Poster now available for download

I'm really proud of the work my colleagues did on the SQL Server 2008 System Views poster in conjunction with our partners. Ron Talmage led our team. But the best news is that it's now available for download for free:

http://www.microsoft.com/downloads/details.aspx?FamilyID=531c53e7-8a2a-4375-8f2f-5d799aa67b5c&displaylang=en

 

SQL Server Certification Statistics

Ken Tanner recently reminded me of the link showing current certification status:

http://www.microsoft.com/learning/mcp/certified.mspx

 

The SQL Server related entries from this list are:

 

MCDBA SQL 2000 152086
MCTS SQL 2005 41665
SQL 2005 BI 2600
SQL 2008 Dev 336
SQL 2008 BI 134
MCITP SQL 2005 DBA 6695
SQL 2005 Dev 2925
SQL 2005 BI 1088
SQL 2008 DBA 92
SQL 2008 BI 50
MCM SQL 2005 18
SQL 2008 2

The 18 SQL 2005 Certified Masters folk have been brought across from the earlier SQL Ranger program. The list doesn't include the SQL 2008 DBA TS certification for some reason. I'd be interested in hearing your thoughts on why there was such a big drop in numbers from the 2000 versions to the 2005 versions. I suspect that part of it has to do with the 2000 exams being much easier and that they were a relatively easy option for those pursuing a variety of certifications, including the developer certifications.

New Sydney CBD Lunch-time SQL Server User Group

Victor Isakov has decided to spin up a new SQL Server user group that will be meeting on the 1st Wednesday of each month, at Westpac Place in the city. He describes the group as focussing on best practices, optimisation, design and implementation and is aiming directly at the local corporate market.

Access to Westpac Place will be quickest if you have pre-registered so Victor is asking you to let him know you're coming by sending him an email to victor@sqlserversolutions.com.au.

The first three meetings for 2009 are as follows:
 
Topic: DBA: Best Practices for All DBAs to Follow
Date: 4th February
Description: As the popularity of SQL Server continues to grow, so is the demand for new SQL Server DBAs. Unfortunately, you can't “go to school” to learn how to become a DBA. Most DBAs learn from books, seminars, short classes, and trial and error. This session is designed to encourage a cross-pollination of ideas and best practices between DBAs from different organizations. It includes best practices from the trenches which can only be obtained through experience.
 
Topic: DBD: A Comprehensive Guide to Indexing in SQL Server
Date: 4th March
Description: With the release of SQL Server 2008 the database developer has more choices in their arsenal for optimally indexing database solutions. In this session we will explore the different indexing technologies that are available in SQL Server 2005 and SQL Server 2008, such as included, composite and filtered indexes. We will discuss the primary design considerations and tradeoffs made when implementing an indexing strategy and present more "exotic" scenarios such as how to implement custom hash indexes, how to combine filtered indexes with sparse columns, partitioned indexes and how to implement partitioned views with different underlying table indexes.
 
Topic: ARCH: Reducing Your Storage Cost in SQL Server 2008
Date: 1st April
Description: Are increasing storage costs hurting your bottom line as data sizes keep growing throughout your organization? In this session, we explore how SQL Server can help you lower your storage costs. We start with Microsoft SQL Server 2005 partitioning to transparently migrate older data to lower-cost storage tiers over time. We then discuss new storage cost-saving functionality in Microsoft SQL Server 2008 such as data compression, backup compression, and sparse columns, filtered indexes, file stream which can get you to 50% or more reduction in storage requirements for typical applications. Throughout the session, we share experiences, insights, and best practices from SQL Server customers and Microsoft-internal deployments. Learn how to make good use of these technologies in combination.

DevWeek – London – March

I'm also locked in to present a one day "SQL 2k8 for Developers" preconference session and three conference sessions for DevWeek in London in March, along with a number of my colleagues from Solid Quality: Itzik Ben-Gan and Javier Loria. I'm really looking forward to it and would love to catch up with any SQL locals while there. Details are at the DevWeek site: http://www.devweek.com.

TechEd US – Full Text Indexing – SQL Server 2008

Got the email today to say I had a session accepted for TechEd US. I'll be doing the "Answering the queries your users really want to ask" session on full text indexing in SQL Server 2008. It's an update of the session that I did at TechEd EMEA in Barcelona recently.

I'm really pleased to get to present again this year, given the vastly reduced number of data track sessions.

LINQ to SQL: Does it have much of a future?

Kevin Kline recently posted, wondering about the directions for LINQ.

When people refer to LINQ, they're often referring to "LINQ to SQL" instead.

LINQ as a language enhancement is pretty cool. It provides a way to easy navigate enumerable objects. The only thing that puzzles me is why they picked SQL-like syntax for this instead of something more appropriate for objects.

On the other hand, LINQ to SQL I can't get excited about. The main issues are that it's tied to SQL Server and only gives you a one-to-one mapping of tables to objects. That leaves you with either a lousy database design or a lousy object model.

The easiest example is anything that involves many to many relationships. If I have a flights table and a passengers table, I'd typically have a linking table (like flightmanifests) that records which passengers are on which flights. That's good database design but I wouldn't want an object model based directly on those three tables. What I'd want at the object level is a passenger object with a flights collection and a flight object with a passengers collection.

The Entity Framework lets you cope with both the issues mentioned and seems to be more likely to be the direction that Microsoft will keep heading. It also adds some interesting constructs in the ESQL language. However, its generic nature means you need to work with a very constrained set of data types. You lose the rich data types available with SQL Server in the trade off with the ability to write more generic code.

From the database end, LINQ to SQL can generate quite poor TSQL and the way people often use it ends up causing plan cache pollution issues, much the same as anyone using AddWithValue() to add parameters to a SqlParameters collection in ADO.NET would.

Every time I show people the TSQL code generated from some simple LINQ to SQL queries, I see two reactions. People with a developer background usually say "I'm glad I didn't have to write that". People with a database background say "No-one should write that – it's horrible code". And LINQ to Entities generates even more generic code (as you would imagine it needs to when it doesn't even know what database engine it's targeting).

This is usually all justified by increased developer productivity. "As long as it works well enough and it's done quickly, who cares if what's going on under the covers isn't great?" And that's 100% true for small or simple applications. However, the places I see Microsoft pushing this technology is to ISVs and large enterprise clients. These are likely to be the people it's least appropriate for.

The ADO.NET group seems to have adopted LINQ to SQL now but I know they really don't love it and the Entity Framework is what they're interested in. So I can't see LINQ to SQL having much of a future at all. LINQ to Entities is much more likely to stay around.

The other big issue I see in this area is maintenance. Microsoft have made big strides with the Database Edition of Visual Studio Team System but every time I ask questions about what the plans are for allowing it to "see" all the mapping files from these coding technologies, I get very blank stares back. Many DBAs can't make any changes to their databases today because they have a sea of Access applications all over the organisation and they don't know what will break when they change something. Are LINQ to SQL and Entity Framework mapping files going to be the next generation of Access-database-style management issues?

Rendering HTML in Reporting Services Text Boxes in SQL Server 2008

Some time back, I posted about how to do this. Well by the time we got to RTM, this had changed.

There isn't a "create placeholder" option when you right-click a cell any more.

When you drag a field into a cell, it creates a placeholder that contains the field. If you right-click the placeholder that was added, you'll see an option to edit the Placeholder Properties. What threw me for a while was that if you right-click the cell, you won't see this option. Turns out you have to right-click the field-name within the cell, not the blank area beside the name within the cell. Thanks to my colleague Jessica Moss for helping me find it.

This really isn't very good UI work in the report designer. It's quite counter-intuitive and different to how cell selection, etc. works in other products like Excel.

December Books Online for SQL Server

I'm always encouraging people to keep up to date with books online. There's another update out now.

I know when you get to this link:

http://www.microsoft.com/downloads/details.aspx?FamilyID=be6a2c5d-00df-4220-b133-29c1e0b6585f&DisplayLang=en 

it says "September" but it really is the one issued in December. (Of course when it's installed, it says November :-))