Thursday, August 30, 2007
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...
Wednesday, August 29, 2007
On by default on a new database, it means index and column statistics are updated 'on the fly'.
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'
Sunday, August 26, 2007
Wednesday, August 22, 2007
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
SQL Server 2005 Remote Connectivity Issue TroubleShoot
Tuesday, August 21, 2007
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 ?;'
Friday, August 17, 2007
‘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 >
Benefits of SQL 2005 Schema addition >
Subdivide database into areas of interest / business area by assigning tables with schema names.
For example in the AdventureWorks database >
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.
One command versus many when granting access to a set of objects when grouping them by schema.
Objects not reliant on users
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.
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.
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.
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),
CREATE TABLE Users
UserId INT NOT NULL PRIMARY KEY
SQL 2000 / 2005 Underlying tables >
| || |
Wednesday, August 15, 2007
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
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]
Saturday, August 4, 2007
-- 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
Thursday, August 2, 2007
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%'