Getting the list of databases that a user has rights to from SQL Server

We have a few applications that let the user select the database connection details.  It’s for SQL Server 2005/2008 and they can pick the server protocol, server name, and the database.  Once they select a database server, the user selects the database from a pick list selection.  We filter that list to only show the databases that are applicable for that user and application.

The first thing we do is get the list of databases.  We want to only display the databases that user can access and filter out the system databases.  There are a few ways to get the list of databases from SQL Server.  There is a system stored procedure named sp_helpdb or you can go right to the same tables and views that sp_helpdb uses.  The latter matter is more efficient, but you want to make sure that you are using the system views instead of the system tables.  Microsoft has deprecated access to the system tables and has published a list that maps the system tables to the system views on this MSDN page.

The system view that we want to uses is sys.databases.  This view will give us a list of all (well nearly all and close enough for our purposes) of the mounted databases.  This includes the system databases and databases that the user may not actually have rights to.  We can filter out the system tables by excluding them by name,  We can use the has_dbacesss() function to filter out the tables that we don’t have access to.  The has_dbacess('databasename') function will return 1 for databases that the current connection has access to, or 0 for no access.  That give us the following T-SQL for getting the list of databases:

select Name 
from sys.databases 
where (has_dbaccess(name) > 0) 
and name not in ('master', 'tempdb', 'model', 'msdb') 
order by 1

If your user account has access to different types of databases and you want to filter the list to provide only the databases that your application supports, then you can go one step further.  Find a table or view in your database that is fairly unique.  If all of the table names are pretty generic, just create with unique name and make a use for it later.

The following SQL statement can be executed from an application to get a filtered list of databases in one call to the server.

create table #TempTable(dbname nvarchar(128));
declare @AppDatabase nvarchar(128);
declare cr cursor for select Name from sys.databases where (has_dbaccess(name) > 0) 

open cr;

fetch next from cr into @AppDatabase;

WHILE @@FETCH_STATUS = 0 begin;
  execute (' insert into #TempTable(dbname) select Table_Catalog from [' + @AppDatabase + '].INFORMATION_SCHEMA.TABLES where TABLE_NAME = ''GeoZoneEdge''');
  fetch next from cr into @AppDatabase;
end;

deallocate cr;

select dbname from #TempTable

drop table #TempTable

The use of semicolons makes it easy to send the set of the t-sql commands as one command to the server.  you would replace the string “yourtablenamehere” with the name of the actual table or view you were trying to match.   By sending a set of commands as a single batch, we get the power of a stored procedure with actually having to have a stored procedure.  The blank lines are there for readability, you can run that as a single execute command and get back a result set.

The first few lines create a temp table to store the database names collected from sys.databases.  We use a cursor to iterate through result set from sys.databases so that we can check each database for the existence of a table that we know will be in our application’s database.   For each database name in that result set, we use the INFORMATION_SCHEMA.TABLES view to check for the existence of a specific table in that table.  Since we doing that lookup for each database, we need to construct the SQL statement at runtime, as shown in line 10 in the listing above.  Each select statement the produces a result, logs the database name into the temporary table.  Then we just return the results of the temporary table.

It looks involved, but it executes fast and the calling application only needs to make one call to the SQL Server to collect the data.

Getting back the Ctrl-N shortcut with SQL Server 2008’s SSMS

After I installed SQL Server 2008, I noticed that the Ctrl-N keyboard shortcut in the SQL Server Management Studio (SSMS) no longer worked.  What it’s supposed to do is to open up a new query tab.  In my install of SSMS 2008, the Ctrl-N key did nothing.  Pressing Ctrl-N was burned into my brain. It was a serious context switch for me to have to select a menu item or press a button to get a new query tab. 

A friend of mine gave me a simple tip that fixed it.  Change the keyboard layout to “SQL Server 2000” and then back to “Standard” and that will restore the default keyboard mappings.  That fixed it and I belatedly found it documented here, here and here.  I don’t know why I didn’t check the series of tubes for the answer, it was pretty obvious how to fix it.

To change the keyboard mappings, select Tools->Options from the SSMS menu and then go to Environment->Keyboard.  From that spot, you can change the keyboard layout.  Change the layout and press the “Ok” button to save the changes.  Then, repeat the steps and select the standard layout and press “Ok” again.  That will set the default keyboard mappings, which will include the Ctrl-N mapping.

Handling collation sequences with temporary tables and table variables with SQL Server

When building complex stored procedures that span multiple tables, you will probably need to store some intermediate results in a local buffer and process them before returning the final output.  SQL Server lets you do this through temporary table and table variables.  

Usually table variables offer faster performance (less locking and logging are required), but they have more restrictions than temporary tables.  Common to both types is where they are located.  When you create a table variable or temporary table, it gets created in the tempdb database, not in the current database.  This can affect the collation sequences applied to character fields  If the SQL Server was installed using one collation and your database uses a different collation, joins from tables in your database with temporary tables will fail if you join on character fields.  The tempdb database will use the server default collation sequence. That collation may not be the same collation used by your database if your created your database on a different server that used a difference collation.  The server collation is used for all of the system databases (including tempdb) and for any newly created user databases.  Databases that are attached or restored from a backup keep the collation that they were created with.

Gregory Larsen posted some sample SQL code on sqlservercentral.com that will demonstrate the error.  If you run the following SQL:

create table #a (char_set1 varchar(50) collate Latin1_General_CI_AS) 
create table #b(char_set2 varchar(50) collate Latin1_General_BIN) 
insert into #a values ('collate') 
insert into #b values ('collate') 
select * from #a join #b on char_set1 = char_set2

You will an error message like the following:

Msg 468, Level 16, State 9, Line 5

Cannot resolve the collation conflict between "Latin1_General_BIN" and "Latin1_General_CI_AS" in the equal to operation.

That example is used to show the type of error you would get in your code.  The actual code that would throw that error would be comparisons between character fields in a temporary table/table variable and with a permanent table in your database.  If you are deploying databases to servers where the server’s default collation sequence could be different than the collation sequence used by your database, then you want to add “COLLATE database_default” to all of your character field definitions when you define a temporary table or table variable.  Using “COLLATE Database_Default” will assign the collation sequence of the current database to the field.  This will allow field comparisons between character fields in temporary tables/table variables and permanent tables to execute with triggering the “collation conflict” error.

Instead of using syntax like:

create table #a(SomeID integer, SomeCharValue varchar(20)) 
  
declare @a TABLE(SomeID integer, SomeCharValue varchar(20)) 

Use the following:

create table #a(SomeID integer, SomeCharValue varchar(20) COLLATE database_default) 
  
declare @a TABLE(SomeID integer, SomeCharValue varchar(20) COLLATE database_default)

This will work no matter what the collation sequence for either tempdb or your database.  You don’t need to query the server to check what the sequence.  The big limitation is that you have to explicitly define the columns in the temporary table.  If you were using SELECT INTO syntax to create the temporary table implicitly by the columns in the SELECT statement, the temporary table will use the collation of tempdb.  You would need to structure the SQL.  For example, if you were using syntax like this:

select name, crdate, filename
into #a
from master.dbo.sysdatabases
drop table #a

You would need to rewrite it to look like this:

create table #a(
  name sysname collate database_default,
  crdate datetime,
  filename nvarchar(260) collate database_default
)
insert #a(name, crdate, filename)
select name, crdate, filename
from master.dbo.sysdatabases
drop table #a

You pay a small penalty by the extra SQL to explicitly define the temporary table, but you gain with having code that will work on any server.

Adding SQL Server logins and user accounts for multiple databases in one pass

We have a few applications that include bits that are service applications.  These applications talk to SQL Server databases and they authenticate using SQL Server logins, as opposed to using Windows Authentication.  We recently changed the account that the services were using to a new account with a more robust password.  When the services are deployed, they will have an update mechanism that will add the new login account.  For internal testing, I wanted to give our testers the ability to add the server login and database user for all of their test databases in one shot.  I ended up with a script that looked like this:

   1: use [master]

   2:  

   3: -- create server login if if does not already exist

   4: if not exists (SELECT 1 FROM sys.sql_logins where name = 'myservice') 

   5: create login myservice with password = 'wEEzPHUxce0xhzQFEeF8OHS2KqYYkxTA', Check_expiration = OFF

   6:  

   7: -- Create a cusror that will let us iterate through a list of user 

   8: -- created databases, skipping over the system databases

   9: declare c cursor fast_forward for 

  10:   select name 

  11:   from master.dbo.sysdatabases 

  12:   where name not in ('master', 'tempdb', 'pubs', 'model', 'msdb')

  13:  

  14: declare @name varchar(128)

  15: declare @sql varchar(800)

  16:  

  17: open c

  18:  

  19: fetch next from c into @name

  20:  

  21: while @@fetch_status = 0

  22: begin

  23:   print @name -- display the name of the database

  24:  

  25:   -- Drop the user and schema. this may need to be augmented depedning how you define your roles

  26:   select @sql = 'use ['+@name+'];if exists (select 1 from information_schema.tables where Table_name in (''SomeTableUniqueToMyApp'', ''AnotherTableUniqueToMyApp'')) if exists (select 1 from sys.database_principals where name = ''myservice'') begin IF EXISTS (SELECT 1 FROM sys.schemas WHERE name = ''myservice'') begin DROP SCHEMA myservice end drop user myservice end'

  27:   execute (@sql)

  28:   

  29:   -- Add the user

  30:   select @sql = 'use ['+@name+'];if exists (select 1 from information_schema.tables where Table_name in (''SomeTableUniqueToMyApp'', ''AnotherTableUniqueToMyApp'')) if not exists (select 1 from sys.database_principals where name = ''myservice'') create user myservice for login myservice'

  31:   execute (@sql)

  32:  

  33:   -- Define the user's role  

  34:   select @sql = 'use ['+@name+'];if exists (select 1 from sys.database_principals where name = ''myservice'') execute sp_addrolemember db_owner, myservice'

  35:   execute (@sql)

  36:   

  37:   fetch next from c into @name

  38: end

  39:  

  40: close c

  41:  

  42: deallocate c

This script starts off in the master database and will require admin access rights to the server.  If we break down the script, we can review what each section does and why it’s doing it

Lines 4-5 will create the login if it does not already exist.  The Check_Expiration option is set to “OFF” to verify that password expiration policy will not be applied to this login account.  The BOL for SQL Server 2005 states that this option defaults to “OFF”, but I like to explicitly set that option for a couple of reasons.  One reason is that it stands out when you read the script.  Another reason would be that a future service pack or later version of SQL Server could default that setting to on.

Lines 9-12 create a cursor that provides a list of databases.  We skip over the system tables to save some processing.

Lines 17-22, 37-42 handle the mechanics of opening up the cursor and iterating through each row in the set of database names.

Lines 26-27 create a dynamic SQL statement that performs the following steps and then executes that statement:

1. Change the database context to the current database that was located by the cursor



2. Uses the information_schema.tables view to use this database only if contains two tables that are known to exist in my application database.  This will prevent the user from being added to the wrong database.



3. If this user has a schema, drop the schema.



4. Drop the user from the database.



5. Execute the dynamically created SQL statement.

Lines 30-31 create a dynamic SQL statement that performs the following steps and then executes that statement:

1. Change the database context to the current database that was located by the cursor



2. Uses the information_schema.tables view to use this database only if contains two tables that are known to exist in my application database.  This will prevent the user from being added to the wrong database.



3. Create the user and map that user to the login account created at line 4



4. Execute the dynamically created SQL statement.

Lines 34-35 create a dynamic SQL statement that performs the following steps and then executes that statement:

1. Change the database context to the current database that was located by the cursor



2. Uses the information_schema.tables view to use this database only if contains two tables that are known to exist in my application database.  This will prevent the user from being added to the wrong database.



3. Adds the user to the db_owner role.  Your application may need fewer rights for the user.



4. Execute the dynamically created SQL statement.

We do give our customers the ability to run with just Windows Authentication, but configuring services to run under domain accounts is an additional set of steps that most of our customers prefer not to use.  It’s a much simpler OOBE if the services use SQL Server authentication.

How to rename database objects to comply with naming conventions

We have guidelines for the naming conventions of our database objects.  It's pretty much the common pattern you see all over the place. For example, we use the following convention for default constraints:

DF_TableName_ColumnName, such as DF_Student_School

Pinal Dave has great set of database coding standards on his site, more in-depth than ours actually.  We have found that using coding standards makes the database schema easier to read and makes it easier to perform schema updates.  If you don't explicitly name a default, SQL Server will name it for you.  And it wont be pretty or consistent across databases.  We have our own tool for pushing out schema updates to our customers and it assumes the object names will be the same for each database.

When we submit our schema updates internally, we usually catch any deviation from our naming conventions.  It's not a perfect process and every now and then, something slips through the cracks.  We then correct the schema update to use the appropriate naming convention.  if we have been using the schema changes internally, we may have some databases that don't match the published schema for the object names.  When that happens, we run a simple T-SQL script that cleans house.  The following T-SQL will scan the database for default constraints that do not match our naming conventions and rename the ones that it finds.

DECLARE @OldName nvarchar(512)
DECLARE @NewName nvarchar(512)
DECLARE @OldToNewName nvarchar(512)

-- We use a table variable to contain the list of objects to be renamed.
DECLARE @FixSchema TABLE(
    sys_OldName [sysname] COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, 
    sys_NewName [sysname] COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL 
)

-- Get the list of current column defaults
-- The OldName column represents the current name of the default
-- The NewName column represents the name of the default using 
-- our naming convention
INSERT INTO  @FixSchema(sys_OldName, sys_NewName)
select o.name AS OldName
,'DF_' + Object_Name(o.parent_obj) + '_' + c.Name AS NewName
FROM sysobjects o
JOIN syscolumns c ON o.parent_obj = c.id AND c.cdefault = o.id
WHERE o.type = 'D' 

-- Declare cursor on this table variable to access on the non-compliant
-- constraints
DECLARE AddDrop CURSOR FOR
SELECT sys_OldName, sys_NewName
   FROM @FixSchema
   where sys_OldName <> sys_NewName

OPEN AddDrop
    
-- Loop through the list of defaults where the default name is not 
-- strongly typed
FETCH NEXT
   FROM AddDrop
   INTO @OldName, @NewName

WHILE @@FETCH_STATUS = 0
BEGIN
  -- Assenble a T-SQL command that can be executed dynamically 
  -- to rename the constraint
  SELECT @OldToNewName = 'sp_rename ' + QUOTENAME(@OldName,'''') + 
    ', ' + QUOTENAME(@NewName,'''') + ', ' + QUOTENAME('object','''')

  -- Print the command to be executed.  Useful for seeing 
  -- what is being done
  PRINT @OldToNewName

  -- Execute the T-SQL to rename the object.  If the EXEC line is
  -- commented out, the code will display what would be changed 
  -- with making any actual changes to the schema
  EXEC(@OldToNewName)
  FETCH NEXT
    FROM AddDrop
    INTO @OldName, @NewName
END

-- Close and deallocate the cursor
CLOSE AddDrop
DEALLOCATE AddDrop

I usually use the INFORMATION_SCHEMA views to peek at the table and column structures.  The INFORMATION_SCHEMA.COLUMNS view will tell which columns have default constraints, but not the name of the constraint. In this case I needed to access the sysobjects table to retrieve the name of the constraint.  The same type of code can be used to rename other objects like check constraints.

Troubleshooting SQL Server connectivity issues

Xinwei Hong (MSFT) wrote a great troubleshooting guide on how to identify and resolve SQL Server connectivity issues on the SQL Protocols blog.

Xinwei broke it down to 6 possible causes:

1. Network issue.
2. SQL Server configuration issue.
3. Firewall issue.
4. Client driver issue.
5. Application configuration issue.
6. Authentication and logon issue.

For each cause. Xinwei lists some background on why that cause could a problem along with specific commands to run to help diagnose the problem.  For example, to determine if #2, SQL Server configuration issue, is the root cause of the problem; the following steps are listed:

You need to make sure the target SQL Server is running and is listening on appropriate protocols. You can use SQL Server Configuration Manager (SCM) to enable protocols on the server machine. SQL Server supports Shared Memory, Named Pipes, and TCP protocols (and VIA which needs special hardware and is rarely used). For remote connection, NP and/or TCP protocols must be enabled. Once you enabled protocols in SCM, please make sure restart the SQL Server.

 

You can open errorlog file to see if the server is successfully listening on any of the protocol. The location of errorlog file is usually under:

%ProgramFile%Microsoft SQL Server/MSSQLxx.xxx/MSSQL/Log

If the target SQL instance is a named instance, you also need to make sure SQL Browser is running on the target machine. If you are not able to access the remote SQL Server, please ask your admin to make sure all these happen.

 

This guide is definitely worth keeping bookmarked.  When a client calls and they can't connect to their database, having a list like the one Xinwei provide should make it easy to resolve the problem.  Or at the very least, rule out the usual suspects.

Dealing with the "Cannot generate SSPI context" error message

One of our sales engineers came up to me with with a problem that I had not come across before.  He was getting the error "Cannot generate SSPI context" when he tried to back up a database. Before getting too deep into the problem, I'll lay out the background of how the problem occurred.

Our applications work with SQL Server 2000 and 2005.  Our desktop applications have the ability to back up the SQL Server database and store the backup on the local machine.  The backup command is issued to the database server, typically on it's own server.  The account that SQL Server runs under typically can only access the local file systems.  You can get around that by running SQL Server under an account with network access, but as a shrink wrapped application, we want to under the default installation of SQL Server.

To get around the file system access, I wrote a win32 service that runs on the same machine as SQL Server.  Our applications back up the database through my agent service.  When the agent receives a backup request from a client, it does the following:

1. Performs some preventative maintenance on the database
2. Defragments the log file
3. Backs up the database to local path
4. Compresses the database backup to a .zip file
5. Sends the compressed backup to the client
6. Deletes the backup and compressed backup from the server.
   

It does a few other things, but those steps are the highlights of the backup process.  Our engineer was getting the "Cannot generate SSPI context" error right at step one.  I have never come across that error so it was time to fire up Google and go searching.  One of the top hits for goggling that error message was a KB article, 811889.  It was informative, but not especially helpful for me.  The top hit was much more helpful, “Cannot generate SSPI context” error message, when connect to local SQL Server outside domain, on the SQL Protocols blog.  Who knew that SQL Protocols had it's own blog.  This post had all of the good details of what was happening and suggestions on how to resolve it.  I like that.

In short that error can occur when all of the following are true:

1. The hosting machine of SQL Server is connected to a network, including home network or dialup connection, but it is disconnected from its domain.
2. The OS of the hosting machine is Windows XP or 2000. Not windows 2003.
3. The connection is to a local SQL Server.
4. Connection configuration causes network library to choose TCP/IP provider.

The root cause is that agent service is using integrated security to connect to the local server over TCP/IP.  The SSPI in the error message stands for Security Support Provider Interface.  SSPI is a set of Windows API that handle delegation and authentication over data transport layers (TCP, Named Pipes, etc).  With TCP/IP and SSPI, the Kerberos protocol is used to authenticate the user account.  This will attempt to access the Active Directory services of the domain that the user is logged into.  If that domain is not accessible, the authentication attempt will fail.  This check will only occur if SSPI detects that it is on a network.  If it's not on a network, it will use NTLM, which for our situation will work just fine.

In our case, the engineer has a laptop and he logs into it with a domain account.  If he's demoing the products at a clients site, he may have a network connection, but not be connected to our domain.  The immediate work around was for him to close his network connection and do his backup.  Literally all he needed to do was to press a button on his laptop to turn off his wireless adapter.

The long term solution will be for me to change conditions #3 or #4.  The code is currently hard coded to connect to a sever named "(local)", I may try replacing that with the TCP/IP loopback address 127.0.0.1.  If that doesn't work, I add a setting that allows the agent service to connect with the Shared Memory or Named Pipes providers.

Know your SQL Server version

There are times where you need to know what version of SQL Server is installed.  Usually you want to know which version and which service pack has been applied.  There have been a few isolated cases over the years where we saw bugs go away or significant performance boosts by merely installing the latest service pack.  It's less of an issue with 2005, but with SQL Server 2000, we wanted to make sure the user installed the latest service packs to block against stuff like the "Slammer" worm.

The following bit of T-SQL will send back the version information in easy to process pieces

SELECT SERVERPROPERTY('productversion') AS ProductVersion, SERVERPROPERTY ('productlevel') AS ProductLevel, SERVERPROPERTY ('edition') AS Edition

For SQL Server 2005, you could get back something like this:

ProductVersion  ProductLevel  Edition
--------------- ------------- ----------------
9.00.2047.00    SP1           Standard Edition

Which indicates the Standard Edition of SQL Server 2005, with Service Pack 1 installed.  You can also get most of that information with

select @@version

But you would have to parse out the version from a block of text like this:

----------------------------------------------------------------------
Microsoft SQL Server 2005 - 9.00.2047.00 (Intel X86) 
 Apr 14 2006 01:12:25 
 Copyright (c) 1988-2005 Microsoft Corporation
 Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 2)

Using SERVERPROPERTY (2000, 2005 or 2008) is much easier than parsing that block of text.  To determine which version is running based just on the version number, Microsoft has a KB article that lists all of the releases under KB321185.