Friday, May 25, 2007

Using a schema collection to validate output of FOR XML RAW

Schema collections are used in SQL Server 2005 to create an xml datatype field that is validated with an XML Schema. Unfortunately there is an xsd feature that is not implemented very nicely is the processContents="lax" attribute. The attribute processContents is implemented, but where normal xsd supports the values strict, lax and skip. The sql server schema collection parser only supports strict and skip. And unfortunately using lax does not degrade, it just gives a compilation error. So reusing prebuild xsd files is going to create some issues that need to be resolved before you can compile them in the database. I will show an example where using the processContents="lax" technique would be usefull. The following schema can be used to create a datatype to store a result of a "for xml raw" query. When you set processContents="lax" you will get a compilation error. Although it would be usefull, we have to be content with processContents="skip".

IF EXISTS(SELECT * FROM sys.schemas WHERE name ='xml_raw_schema')
    DROP XML SCHEMA COLLECTION  dbo.xml_raw_schema
 N'<?xml version="1.0"?>
 <xsd:schema xmlns:targetNamespace=""
   <xsd:element name="table">
           <xsd:element name="row" minOccurs="0" maxOccurs="unbounded">
                 <xsd:anyAttribute processContents="skip" />

You can use the schema collection like this:
declare @xml xml(xml_raw_schema)
SET @xml = (select * from sys.tables FOR XML RAW, TYPE, ROOT('table'))
select @xml
Which shows (in my test case) the following output:
  <row name="Orders" object_id="2073058421" schema_id="1" parent_object_id="0" type="U " type_desc="USER_TABLE" create_date="2007-04-12T17:30:08.150" modify_date="2007-04-12T17:46:17.043" is_ms_shipped="0" is_published="0" is_schema_published="0" lob_data_space_id="0" max_column_id_used="3" lock_on_bulk_load="0" uses_ansi_nulls="1" is_replicated="0" has_replication_filter="0" is_merge_published="0" is_sync_tran_subscribed="0" has_unchecked_assembly_data="0" text_in_row_limit="0" large_value_types_out_of_row="0" />
Although we have to be carefull that it does not slow down processing too much I find it a very valuable technique because you can achieve a very basic level of type safety.

Thursday, May 24, 2007

Wouldn't it be usefull if sp_who showed the query the user is running.

I can't remember the number of times I have run sp_who2 and then dbcc inputbuffer (##) to quickly check out blocking problems. Wouldn't it be usefull if this information was included in sp_who. Well, with SQL Server 2005 we can finally do this thanks to the power of the function sys.dm_exec_sql_text(). All I really had to change to sp_who2 was adding "OUTER apply sys.dm_exec_sql_text(s.sql_handle)" and then use the stmt_start and stmt_end fields to extract the substring from the query.

Install the procedure below and run:
exec sp_whoo
Executing the procedure will give you the following results:
SPIDStatus Login HostNameBlkByDBName Command CPUTimeDiskIOLastBatch ProgramName SPIDREQUESTIDSqlStatement
52 sleepingsaMyLaptop . test_dbAWAITING COMMAND110 43 05/24 10:54:40Microsoft SQL Server Management Studio - Query520 select * from sys.tables

Changes versus the standard sp_who2 in SQL Server 2005:

  • Removed all spids before 50 (system threads)
  • Removed your own spid.


  • Queries running through the service broker do not always return a sql handle. So no data can be shown.
  • When accessing encrypted objects, no information can be shown either
You can download the script sp_whoo.sql or read the code below:

USE master
IF (EXISTS (SELECT * FROM sysobjects WHERE name = 'sp_whoo' AND OBJECTPROPERTY(id, 'IsProcedure') = 1))
      DROP PROCEDURE dbo.sp_whoo
CREATE PROCEDURE dbo.sp_whoo(@loginame sysname = NULL)
declare @retcode         int
declare @sidlow         varbinary(85)
       ,@sidhigh        varbinary(85)
       ,@sid1           varbinary(85)
       ,@spidlow         int
       ,@spidhigh        int
       ,@spidself        int
declare @charMaxLenLoginName      varchar(6)
       ,@charMaxLenDBName         varchar(6)
       ,@charMaxLenCPUTime        varchar(10)
       ,@charMaxLenDiskIO         varchar(10)
       ,@charMaxLenHostName       varchar(10)
       ,@charMaxLenProgramName    varchar(10)
       ,@charMaxLenLastBatch      varchar(10)
       ,@charMaxLenCommand        varchar(10)
declare @charsidlow              varchar(85)
       ,@charsidhigh             varchar(85)
       ,@charspidlow              varchar(11)
       ,@charspidhigh             varchar(11)
SELECT @retcode = 0      -- 0=good ,1=bad.
      ,@sidlow  = convert(varbinary(85), (replicate(char(0), 85)))
      ,@sidhigh = convert(varbinary(85), (replicate(char(1), 85)))
SELECT @spidlow         = 50
      ,@spidhigh        = 32767
      ,@spidself        = @@spid
IF (@loginame IS     NULL)  --Simple default to all LoginNames.
-- select @sid1 = suser_sid(@loginame)
IF EXISTS(SELECT * FROM sys.syslogins WHERE loginname = @loginame)
    SELECT @sid1 = sid FROM sys.syslogins WHERE loginname = @loginame
IF (@sid1 IS NOT NULL)  --Parm is a recognized login name.
   SELECT @sidlow  = suser_sid(@loginame)
         ,@sidhigh = suser_sid(@loginame)
IF (lower(@loginame collate Latin1_General_CI_AS) IN ('active'))  --Special action, not sleeping.
   SELECT @loginame = lower(@loginame collate Latin1_General_CI_AS)
IF (patindex ('%[^0-9]%' , isnull(@loginame,'z')) = 0)  --Is a number.
             @spidlow   = convert(int, @loginame)
            ,@spidhigh  = convert(int, @loginame)
SELECT @retcode = 1
--------------------  Capture consistent sysprocesses.  -------------------
SELECT s.spid , s.status ,s.sid ,s.hostname ,s.program_name ,s.cmd ,s.cpu ,s.physical_io ,s.blocked ,s.dbid
 , convert(sysname, rtrim(s.loginame)) AS loginname
 , s.spid AS 'spid_sort'
 , substring( convert(varchar, s.last_batch, 111) ,6  ,5 ) + ' ' + substring(convert(varchar, s.last_batch, 113), 13, 8 ) AS 'last_batch_char'
 , s.request_id
 , case when s.stmt_start=0 AND s.stmt_end=0 then qt.text
        else substring(qt.text, (s.stmt_start/2)+1,
                (case when s.stmt_end = -1
                      then len(convert(nvarchar(max), qt.text))*2
                      else s.stmt_end 
                 end - s.stmt_start) / 2) 
   end AS 'sql_statement'
INTO #tb1_sysprocesses
FROM master.dbo.sysprocesses s WITH (nolock)
  OUTER apply sys.dm_exec_sql_text(s.sql_handle) AS qt
WHERE ((s.spid > @spidlow AND s.spid < @spidhigh) OR (s.spid = @spidlow AND s.spid = @spidhigh))
  AND s.spid <> @spidself
  AND (s.sid = @sidlow OR @sidlow <> @sidhigh)
IF @@error <> 0
        SELECT @retcode = @@error
--------Screen out any rows?
IF (@loginame IN ('active'))
    DELETE #tb1_sysprocesses
    WHERE lower(status) = 'sleeping'
      AND blocked       = 0
--------Prepare to dynamically optimize column widths.
    @charsidlow     = convert(varchar(85),@sidlow)
   ,@charsidhigh    = convert(varchar(85),@sidhigh)
   ,@charspidlow    = convert(varchar, @spidlow)
   ,@charspidhigh   = convert(varchar, @spidhigh)
     @charMaxLenLoginName   = convert( varchar, isnull( max( datalength(loginname)) ,5))
    ,@charMaxLenDBName      = convert( varchar, isnull( max( datalength( rtrim(convert(varchar(128),db_name(dbid))))) ,6))
    ,@charMaxLenCPUTime     = convert( varchar, isnull( max( datalength( rtrim(convert(varchar(128),cpu)))) ,7))
    ,@charMaxLenDiskIO      = convert( varchar, isnull( max( datalength( rtrim(convert(varchar(128),physical_io)))) ,6))
    ,@charMaxLenCommand     = convert( varchar, isnull( max( datalength( rtrim(convert(varchar(128),cmd)))) ,7))
    ,@charMaxLenHostName    = convert( varchar, isnull( max( datalength( rtrim(convert(varchar(128),hostname)))) ,8))
    ,@charMaxLenProgramName = convert( varchar, isnull( max( datalength( rtrim(convert(varchar(128),program_name)))) ,11))
    ,@charMaxLenLastBatch   = convert( varchar, isnull( max( datalength( rtrim(convert(varchar(128),last_batch_char)))) ,9))
FROM #tb1_sysprocesses
--------Output the report.
SET nocount off
             SPID          = convert(char(5),spid)
            ,Status        =
                  CASE lower(status)
                     When ''sleeping'' Then lower(status)
                     Else                   upper(status)
            ,Login         = substring(loginname,1,' + @charMaxLenLoginName + ')
            ,HostName      =
                  CASE hostname
                     When Null  Then ''  .''
                     When '' '' Then ''  .''
                     Else    substring(hostname,1,' + @charMaxLenHostName + ')
            ,BlkBy         =
                  CASE               isnull(convert(char(5),blocked),''0'')
                     When ''0'' Then ''  .''
                     Else            isnull(convert(char(5),blocked),''0'')
            ,DBName        = substring(case when dbid = 0 then null when dbid <> 0 then db_name(dbid) end,1,' + @charMaxLenDBName + ')
            ,Command       = substring(cmd,1,' + @charMaxLenCommand + ')
            ,CPUTime       = substring(convert(varchar,cpu),1,' + @charMaxLenCPUTime + ')
            ,DiskIO        = substring(convert(varchar,physical_io),1,' + @charMaxLenDiskIO + ')
            ,LastBatch     = substring(last_batch_char,1,' + @charMaxLenLastBatch + ')
            ,ProgramName   = substring(program_name,1,' + @charMaxLenProgramName + ')
            ,SPID          = convert(char(5),spid)  --Handy extra for right-scrolling users.
            ,REQUESTID     = convert(char(5),request_id)
            ,SqlStatement  = rtrim(ltrim(replace(sql_statement, char(13)+char(10), '' '')))
             #tb1_sysprocesses  --Usually DB qualification is needed in exec().
      -- (Seems always auto sorted.)   order by spid_sort
IF (object_id('tempdb..#tb1_sysprocesses') IS NOT NULL)
            DROP TABLE #tb1_sysprocesses
RETURN @retcode -- sp_whoo

Tuesday, May 22, 2007

Fix SQL Server management studio Ctrl+Tab annoyance

In SQL Server 2000 Query analyzer hitting Ctrl+Tab would advance you one query window in the list of open query windows. Unfortunately In Sql Server Management Studio this will display the default Visual Studio Tab overview window. This window has one huge flaw and that is that it only displays the first 25 characters of the description of the open window. Which results in the Case of SQL Server Management Studio in a list with 10 times the same item.
Management Studio lame alt-tab window.
To revert the Ctrl+Tab and the Ctrl+Shift+Tab behaviour back to the way it was in previous versions of the SQL Server client tools you can do the following hack. Close all instances of SQL Server Management Studio and open the following xml file in a text editor:

C:\Documents and Settings\<your user name>\My Documents\SQL Server Management Studio\Settings\CurrentSettings-####-##-##.vssettings

Note that the ####-##-## characters form a date (most likely the installation/service pack installation date.) and you sometimes can have multiple versions of this file. Updating the file with the latest date should work. (I have no pc's with multiple versions of this file.) In the file look for the following string. (The file will have a few very long lines of text in it.)
<UserShortcuts />
Once you found it, replace it with the xml code below and when you restart Management studio using Ctrl+Tab and Ctrl+Shift+Tab will once again switch you to the following or previous code window without showing the useless dialog.
  <RemoveShortcut Command="Window.NextDocumentWindowNav" Scope="Global">Ctrl+Tab</RemoveShortcut>
  <Shortcut Command="Window.NextDocumentWindow" Scope="Global">Ctrl+Tab</Shortcut> 
  <Shortcut Command="Window.NextDocumentWindow" Scope="Query Designer">Ctrl+Tab</Shortcut>
  <RemoveShortcut Command="Window.PreviousDocumentWindowNav" Scope="Global">Ctrl+Shift+Tab</RemoveShortcut>
  <Shortcut Command="Window.PreviousDocumentWindow" Scope="Global">Ctrl+Shift+Tab</Shortcut>
  <Shortcut Command="Window.PreviousDocumentWindow" Scope="Query Designer">Ctrl+Shift+Tab</Shortcut>
Credit: I found this tip burried in a comment on this page of the Microsoft connect website. So thanks to ayeltsov for suggesting the solution to an annoying problem.

Monday, May 21, 2007

Why is viewing a partitioned table layout so difficult

For some reason the Microsoft SQL Server developers decided not to provide any tools to see how a partitioned table is built. Since it is quite important information I wrote a little sp_help_partition TSQL stored procedure to give an overview. There is an optional object parameter which allows you to filter on a specific partitioned table, or shows you all the partitions in the system.

The procedure is installed on the master database, but because the name starts with sp_... you can run it in any database.

   1:  exec sp_help_partition 'Orders'
The result of this statement could be:
TablePartition SchemePartition FunctionPartitionNumberFileGroupNameRangeValue
Ordersmonthly_schememonthly_bucket1History2006-06-01 00:00:00.000
Ordersmonthly_schememonthly_bucket2Partition_022006-07-01 00:00:00.000
Ordersmonthly_schememonthly_bucket3Partition_032006-08-01 00:00:00.000
Ordersmonthly_schememonthly_bucket4Partition_042006-09-01 00:00:00.000
Ordersmonthly_schememonthly_bucket5Partition_052006-10-01 00:00:00.000
Ordersmonthly_schememonthly_bucket6Partition_062006-11-01 00:00:00.000
Ordersmonthly_schememonthly_bucket7Partition_072006-12-01 00:00:00.000
Ordersmonthly_schememonthly_bucket8Partition_082007-01-01 00:00:00.000
Ordersmonthly_schememonthly_bucket9Partition_092007-02-01 00:00:00.000
Ordersmonthly_schememonthly_bucket10Partition_102007-03-01 00:00:00.000
Ordersmonthly_schememonthly_bucket11Partition_112007-04-01 00:00:00.000
Ordersmonthly_schememonthly_bucket12Partition_122007-05-01 00:00:00.000
Ordersmonthly_schememonthly_bucket13Partition_012999-01-01 00:00:00.000

You can download the script sp_help_partition.sql or read the code below:
   1:  USE master
   2:  IF EXISTS(SELECT * FROM sys.objects WHERE name = 'sp_help_partition')
   3:    BEGIN
   4:      PRINT 'Dropping procedure sp_help_partition...'
   5:      DROP PROCEDURE sp_help_partition 
   6:    END
   7:  print 'Creating procedure sp_help_partition...'
   8:  GO 
   9:  CREATE PROCEDURE sp_help_partition (@object_name sysname = NULL)
  10:  AS
  12:  declare @db_name sysname
  13:  select @db_name = db_name()
  15:  if @object_name IS NULL
  16:   begin
  17:      exec ('use ' + @db_name + '
  18:      select 
  19:            object_name(i.[object_id]) as [Table],
  20:                      as PartitionScheme,
  21:                      as PartitionFunction,
  22:            dds.destination_id         as PartitionNumber,
  23:            fg.Name                    as FileGroupName,
  24:            prv.value                  as RangeValue
  25:      from .sys.indexes i 
  26:      inner join sys.partition_schemes ps on ps.data_space_id = i.data_space_id
  27:      inner join sys.partition_functions pf on ps.function_id = pf.function_id
  28:      inner join sys.destination_data_spaces dds on dds.partition_scheme_id = ps.data_space_id
  29:      inner join sys.filegroups fg on fg.data_space_id = dds.data_space_id
  30:      left  join sys.partition_range_values prv on prv.boundary_id = dds.destination_id
  31:      where i.type = 1 ')
  32:    end
  33:  else
  34:    begin
  35:      exec ('use ' + @db_name + '
  36:      select 
  37:            object_name(i.[object_id]) as [Table],
  38:                      as PartitionScheme,
  39:                      as PartitionFunction,
  40:            dds.destination_id         as PartitionNumber,
  41:            fg.Name                    as FileGroupName,
  42:            prv.value                  as RangeValue
  43:      from sys.indexes i 
  44:      inner join sys.partition_schemes ps on ps.data_space_id = i.data_space_id
  45:      inner join sys.partition_functions pf on ps.function_id = pf.function_id
  46:      inner join sys.destination_data_spaces dds on dds.partition_scheme_id = ps.data_space_id
  47:      inner join sys.filegroups fg on fg.data_space_id = dds.data_space_id
  48:      left  join sys.partition_range_values prv on prv.boundary_id = dds.destination_id
  49:      where i.type = 1 and 
  50:            i.[object_id] = object_id(''' + @object_name + ''')')
  51:    end
  52:  RETURN(0)
  53:  GO
  54:  IF EXISTS (SELECT * FROM sysobjects WHERE [name] = 'sp_help_partition' AND type = 'P')
  55:    BEGIN
  56:      PRINT 'PROCEDURE sp_help_partition has been created...'
  57:    END
  58:  ELSE
  59:    BEGIN
  60:      PRINT 'PROCEDURE sp_help_partition has NOT been created due to errors...'
  61:    END
  62:  GO
For more information about table partitioning in Microsoft SQL Server 2005 read the excellent whitepaper by Kimberley Tripp

Wednesday, May 16, 2007

Fix the user/login sid mismatch when restoring a SQL Server Database on a different server.

When you take a backup of a SQL Server database one of the tables that is saved in the backup is called "sysusers". This table is used to link a server login to a database user. To do this, SQL Server links the "sid" field of the sysusers table to the "sid" field of the syslogins table in the master database.

select name, sid from sysusers
select name, sid from syslogins
When you restore the database backup on another SQL Server, the "sid" fields are out of sync and SQL Server does not fix this automatically. This is a big problem because your database users are pointing to logins that don't exist or worse, pointing to the wrong login. To fix this in a simple way you can run the following script:
   1:  DECLARE @username sysname
   3:  SELECT top 1 @username = name 
   4:  FROM sysusers 
   5:  WHERE issqluser = 1 and (sid is not null and sid <> 0x0) and suser_sname(sid) is null 
   8:  WHILE @username is not null
   9:    BEGIN
  10:      IF @username='dbo'
  11:        BEGIN
  12:          PRINT N'mapped sa to the dbo user...'
  13:          EXEC sp_changedbowner 'sa' 
  14:        END
  15:      ELSE
  16:        BEGIN
  17:          PRINT N'mapped ' + @username + ' to the dbo user...'
  18:          EXEC sp_change_users_login 'update_one', @username, @username
  19:        END
  20:      SELECT @username = null
  22:      SELECT TOP 1 @username = name 
  23:      FROM sysusers 
  24:      WHERE issqluser = 1 and (sid is not null and sid <> 0x0) and suser_sname(sid) is null 
  25:    END
The script simply tries to link the users to the logins based on the name, and it tries to make sa the default database user. Whew, problems fixed you would think... Not quite. You still have a bunch of users left in your database that are not linked to any logins. To remove this we can use the following script.
   1:  DECLARE @username sysname
   3:  SELECT top 1 @username = 
   4:  FROM sysusers su
   5:    left join master..syslogins sl
   6:    on =
   7:  WHERE su.issqluser = 1 and (su.sid is not null and su.sid <> 0x0) 
   8:    and suser_sname(su.sid) is null  
   9:    and is null
  11:  WHILE @username is not null
  12:    BEGIN
  13:      print N'Dropping user ' + @username + '...'
  14:      exec sp_dropuser @username
  16:      SELECT @username = null
  17:      SELECT top 1 @username = 
  18:      FROM sysusers su
  19:        left join master..syslogins sl
  20:        on =
  21:      WHERE su.issqluser = 1 and (su.sid is not null and su.sid <> 0x0) 
  22:        and suser_sname(su.sid) is null  
  23:        and is null
  24:    END
To make using this code more practical I wrapped it in a stored procedure which you can install on the master database. You can download the script sp_fix_user_login_mismatch.sql or read the code below:
   1:  IF object_id('dbo.sp_fix_user_login_mismatch') IS NOT NULL
   2:    BEGIN
   3:      DROP PROCEDURE dbo.sp_fix_user_login_mismatch
   4:    END
   5:  GO
   6:  CREATE PROCEDURE dbo.sp_fix_user_login_mismatch
   7:  AS 
   9:  DECLARE @username sysname
  11:  -- Map all the database users to the logins that have the same name.
  12:  SELECT top 1 @username = 
  13:  FROM sysusers su
  14:    inner join master..syslogins sl
  15:    on =
  16:  WHERE su.issqluser = 1 and (su.sid is not null and su.sid <> 0x0) 
  17:    and suser_sname(su.sid) is null 
  19:  WHILE @username is not null
  20:    BEGIN
  21:      IF @username='dbo'
  22:        BEGIN
  23:          PRINT N'mapped sa to the dbo user...'
  24:          EXEC sp_changedbowner 'sa' 
  25:        END
  26:      ELSE
  27:        BEGIN
  28:          PRINT N'mapped the login "' + @username + '" to the database user "' + @username + '"...'
  29:          EXEC sp_change_users_login 'update_one', @username, @username
  30:        END
  31:      SELECT @username = null
  33:     SELECT top 1 @username = 
  34:      FROM sysusers su
  35:        inner join master..syslogins sl
  36:        on =
  37:      WHERE su.issqluser = 1 and (su.sid is not null and su.sid <> 0x0) 
  38:        and suser_sname(su.sid) is null 
  39:    END
  41:  -- Remove all the database users that do not have a matching login.
  42:  SELECT top 1 @username = 
  43:  FROM sysusers su
  44:    left join master..syslogins sl
  45:    on =
  46:  WHERE su.issqluser = 1 and (su.sid is not null and su.sid <> 0x0) 
  47:    and suser_sname(su.sid) is null  
  48:    and is null
  50:  WHILE @username is not null
  51:    BEGIN
  52:      print N'Dropping user ' + @username + '...'
  53:      exec sp_dropuser @username
  55:      SELECT @username = null
  56:      SELECT top 1 @username = 
  57:      FROM sysusers su
  58:        left join master..syslogins sl
  59:        on =
  60:      WHERE su.issqluser = 1 and (su.sid is not null and su.sid <> 0x0) 
  61:        and suser_sname(su.sid) is null  
  62:        and is null
  63:    END
  64:  RETURN(0)
  65:  GO
I'm not a fan of giving you a procedure and leaving it to you to figure out if it is good code or not. So to unit test it I have used the following code
   3:  -- Test harness:
   4:  -- 
   5:  -- Create 3 logins to use as basis for our database users
   6:  -- Create a test database 
   7:  -- map the dbo owner to the fakesa login
   8:  -- add 2 users to the test database based on our 2 logins
   9:  -- backup the test database and drop it
  10:  -- drop fakesa and orphanedlogintest
  11:  -- restore the database and execute our fix procedure.
  12:  -- the user logintest should be remapped
  13:  -- the dbo user should be pointing to sa
  14:  -- the users fakesa and orphanedlogintest should be gone
  17:  set nocount on
  18:  create login fakesa WITH password = 'a D1ff1cult passw0rd'
  19:  create login logintest WITH password = 'a D1ff1cult passw0rd'
  20:  create login orphanedlogintest WITH password = 'a D1ff1cult passw0rd'
  21:  create database testloginproblems
  22:  GO
  23:  use testloginproblems
  24:  EXEC sp_changedbowner 'fakesa' 
  25:  exec sp_adduser 'logintest', 'logintest', 'db_datareader'
  26:  exec sp_adduser 'orphanedlogintest', 'orphanedlogintest', 'db_datareader'
  27:  use master
  28:  EXEC sp_addumpdevice 'disk', 'TestLoginProblemsData', 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\BACKUP\TestLoginProblemsData.bak'
  29:  backup database testloginproblems TO TestLoginProblemsData
  30:  drop database testloginproblems
  31:  drop login fakesa
  32:  drop login logintest
  33:  drop login orphanedlogintest
  34:  create login logintest WITH password = 'a D1ff1cult passw0rd'
  35:  RESTORE DATABASE testloginproblems FROM TestLoginProblemsData
  37:  use testloginproblems
  38:  print N''
  39:  SELECT as 'list of problematic users'
  40:  FROM sysusers su
  41:  WHERE su.issqluser = 1 and (su.sid is not null and su.sid <> 0x0) 
  42:    and suser_sname(su.sid) is null 
  44:  exec sp_fix_user_login_mismatch
  46:  print N''
  47:  SELECT ul.uname as 'user name to check', as 'database user', 'server login'
  48:  FROM (select uname = 'logintest' union all select uname = 'dbo' union all select uname = 'fakesa' union all select uname = 'orphanedlogintest') ul
  49:    left join sysusers su
  50:    on ul.uname =
  51:    left join master..syslogins sl
  52:    on su.sid = sl.sid
  54:  use master  
  55:  EXEC sp_dropdevice 'TestLoginProblemsData', 'delfile'
  56:  drop database testloginproblems
  57:  drop login logintest