Login Failures : Blank Username

Login failed for user ''. The user is not associated with a trusted SQL Server connection.

This occurs on own or in conjuction with other errors , e.g. SSPI.
Bottom line is that it is a Windows Authentication error.

Therefore, nag the networks team or get stuck in with debugging yourself...

Statistics Options

A couple of database level options regarding statistics -

AUTO_UPDATE_STATISTICS

On by default on a new database, it means index and column statistics are updated 'on the fly'.

AUTO_UPDATE_STATISTICS_ASYNC

Queries are run against the current statistics, rather than waiting fot them to be updated first.
The statistics are triggered to be updated asap, hopefully in time for the next query.

I havent (yet) found a system where index updates futher cause poor performance but am consious it could occur on a high volume system.

Setting index statistics options for all databases >

sp_msforeachdb @command1='ALTER DATABASE ? SET AUTO_UPDATE_STATISTICS ON'
sp_msforeachdb @command1='ALTER DATABASE ? SET AUTO_UPDATE_STATISTICS_ASYNC ON'

List SQL Instances on the network

List SQL Instances on the network >

c:\> osql -L

Only discovers servers that want to be found, i.e. where the SQL browser service is broadcasting their existance, and they are on the same subnet etc

Login Failures : SSPI Errors

" SSPI handshake failed with error code 0x80090311 while establishing a connection with integrated security; the connection has been closed. [CLIENT: ip address] "

0x80090311 means "No authority could be contacted for authentication"

This is a Kerberos error and means the user cannot contact AD (active directory) to get a ticket.


Troubleshooting Kerberos Errors
http://www.microsoft.com/technet/prodtechnol/windowsserver2003/technologies/security/tkerberr.mspx

SQL Server 2005 Remote Connectivity Issue TroubleShoot
http://blogs.msdn.com/sql_protocols/archive/2006/09/30/SQL-Server-2005-Remote-Connectivity-Issue-TroubleShooting.aspx

Empty Database Script

As an alternative to scripting, and to see operational issues encountered, I'm creating a 'Delete All Data' script.
This is primarily so I have a test database to use with Replication, Service Broker etc.
In this example I'm creating AdventureWorksTarget, an empty shell of the sample database for sql 2005.

1 Backup AdventureWorks

2 Restore as AdventureWorksTarget

3 Drop any views.
I'm adding this step as this is only a test, and the SCHEMABINDING option on the views preventing me from deleting from some tables.
Use this dynamic SQL to generate the sql, then run it >

SELECT 'DROP VIEW [' + S.name + '].[' + V.name + ']'
FROM sys.views V
JOIN sys.schemas S
ON V.SCHEMA_ID = S.SCHEMA_ID

4 Create supporting procedures -
USP_DropXMLIndexes (code here)
USP_DropTableConstraints_2005 (code here)

5 Clear down XML Indexes and table constraints.
The order is important here, i.e -
1 Remove XML Indexes
2 Remove CHECK constraints
3 Remove FOREIGN KEY constraints
4 Remove PRIMARY KEY constraints

This dynamic sql calls the supporting procedures in the correct order.
Run it, then resulting sql it produces.

SELECT Command FROM
( SELECT Command = 'EXEC USP_DropXMLIndexes ''' + TABLE_SCHEMA + ''', ''' + TABLE_NAME + '''', 0 as SortOrder
FROM   INFORMATION_SCHEMA.TABLES
WHERE TABLE_CATALOG = 'AdventureWorksTarget'
UNION
SELECT Command = 'EXEC USP_DropTableConstraints_2005 ''' + TABLE_CATALOG + ''', ''' + TABLE_SCHEMA + ''', ''' + TABLE_NAME + ''', ''CHECK''', 1 as SortOrder
FROM   INFORMATION_SCHEMA.TABLES
WHERE TABLE_CATALOG = 'AdventureWorksTarget'
UNION
SELECT Command = 'EXEC USP_DropTableConstraints_2005 ''' + TABLE_CATALOG + ''', ''' + TABLE_SCHEMA + ''', ''' + TABLE_NAME + ''', ''FOREIGN KEY''', 2 as SortOrder
FROM   INFORMATION_SCHEMA.TABLES
WHERE TABLE_CATALOG = 'AdventureWorksTarget'
UNION
SELECT Command = 'EXEC USP_DropTableConstraints_2005 ''' + TABLE_CATALOG + ''', ''' + TABLE_SCHEMA + ''', ''' + TABLE_NAME + ''', ''PRIMARY KEY''', 3 as SortOrder
FROM   INFORMATION_SCHEMA.TABLES
WHERE TABLE_CATALOG = 'AdventureWorksTarget' ) CommandBlock
ORDER BY
SortOrder, Command

6 We can finally delete the data in all the tables -

EXECUTE sp_MSforeachtable 'TRUNCATE TABLE ?;'

SQL 101 : SQL 2005 Schemas

‘User Schema Separation’ is allegedly an ANSI SQL 1999 standard but documentation on ansi standards is not freely available on the web.

2000 : [server].[database].[owner].object

2005 : [server].[database].[schema].object

Prior to SQL 2005, each database object had 1 owner.

In SQL Server terminology, the concept of a Schema was introduced in SQL 2005 to provide a logical way to group database objects, ease administration and address security.

Benefits of SQL 2005 Schema addition >

Database Structure	Organization Subdivide database into areas of interest / business area by assigning tables with schema names. For example in the AdventureWorks database > HumanResources.Employee Person.Address Sales.CreditCard This is especially helpful in databases with a large number of tables,
Easier Assignment of permissions	Logical Permissions Grouping Permissions assigned at schema level make it easier to control relevant access to developers etc by subject area. Reduced Administration One command versus many when granting access to a set of objects when grouping them by schema.
Objects not reliant on users	Duplicate Objects In SQL 2000, objects being assigned to users means that 2 users could create objects with the same name which is confusing to say the least. Removing Users Objects not being directly tied to user accounts means that dropping user accounts is much easier. It can be accomplished without either dropping all the owned objects or changing the object owner for any linked objects. Users that OWN a schema now cannot be dropped however without changing the schema ownership.
Ease of teamworking	Multiple users can share a schema Multiple users can OWN a schema through a role or group membership. ‘Default Schema’ setting at user level prevents users having to explicitly reference a schema, for example – if they always use they same one.
Security	The addition of schemas means that combined with object permissions, many more levels of security can be achieved. Access to system objects can now be controlled by a user’s permissions on the SYS schema, unless of course their server role overrides this.
Script Order	Encompassing create statements for related objects inside the same schema makes scripting order unimportant. For example > CREATE SCHEMA UserInfo CREATE TABLE Logins ( UserId INT NOT NULL REFERENCES Users (UserId), LoginDate DATETIME ) CREATE TABLE Users ( UserId INT NOT NULL PRIMARY KEY ) GO

SQL 2000 / 2005 Underlying tables >

	2000 table	2005 tables
Server level	syslogins	sys.server_principals
Database level	sysusers	sys.database_principals sys.schemas

Auditing Groups Membership from SQL Server

MSSQLTips publish this cursor based solution for seeing group membership from within SQL.

I have enhanced it to -
1) Not Error (the sql 2005 try/catch statements)
2) Utilise a table variable to store the results
3) Return a queryable recordset

Note :
This sql reveals accounts that are members of Windows group logins on this server.
It does not reveal group members in child domains or on other domains.

DECLARE  @SqlGroupMembership  TABLE(
   ACCOUNT_NAME      SYSNAME,
   ACCOUNT_TYPE      VARCHAR(30),
   ACCOUNT_PRIVILEGE VARCHAR(30),
   MAPPED_LOGIN_NAME SYSNAME,
   PERMISSION_PATH   SYSNAME
   )

DECLARE @LoginName sysname

DECLARE cur_Loginfetch CURSOR FOR   
SELECT [NAME] FROM master.sys.server_principals WHERE TYPE = 'G'
OPEN cur_Loginfetch
FETCH NEXT FROM cur_Loginfetch INTO @LoginName
WHILE @@FETCH_STATUS = 0
  BEGIN
    BEGIN TRY
    -- Insert found users into table variable
    INSERT INTO @SqlGroupMembership (ACCOUNT_NAME,ACCOUNT_TYPE,ACCOUNT_PRIVILEGE,MAPPED_LOGIN_NAME,PERMISSION_PATH)
      EXEC xp_logininfo @LoginName , 'members'
    END TRY

    BEGIN CATCH
    -- Action for if insert fails
      END CATCH

      FETCH NEXT FROM cur_Loginfetch INTO @LoginName
  END
CLOSE cur_Loginfetch
DEALLOCATE cur_Loginfetch
 
SELECT @@servername as servername,* FROM @SqlGroupMembership ORDER BY [PERMISSION_PATH], [ACCOUNT_NAME]

Moving tables to a new schema

-- Moving tables to a new schema

-- step 1 - create the schema

create schema [migration]

-- step 2 - use sql to build the sql for tables to transfer
select 'alter schema [migration] transfer dbo.' + name + ';' from sys.tables

-- step 3 - run the sql generated by step 2

SQL 2005 : Granting multiple View Definitions

Granting Permissions to view stored procedure definitions.
Useful for letting developers see what a proc does, without permissions to mess it up -

select 'GRANT VIEW DEFINITION ON [dbo].['+routine_name+'] TO [Domain\User]'
from information_schema.routines
where routine_type = 'procedure'
--and routine_name like 'usp%'