New Security Catalog Views in SQL Server 2005/2008

SQL Server 2005 introduced the concept of securables, which are anything within SQL Server which you can assign permissions against. This includes the SQL Server itself. Microsoft did this to provide more granular access in order to customize security to what you need. But with more flexibility comes more complexity. As a result, you've got to look in a few more places to understand the permissions a given login or database user has.

The use of stored procedures for roles such as sp_helprolemember and sp_helpsrvrolemember is still necessary because some permissions are assigned directly to the roles. Some roles, like db_datareader and db_datawriter, have implicit rights that will need to be taken into account. Those should stay a staple of your toolset when trying to determine security within SQL Server. The reason sp_helprotect is no longer viable at the database level is because it is included for backward compatibility and therefore only reports on those securables that were present in SQL Server 2000, such as tables, views, functions, and the like. Newer securables, such as schemas and databases, weren't in SQL Server 2000 and as a result, sp_helprotect won't report permissions on them because it doesn't report against any securable that wasn't present in SQL Server 2000.

http://www.mssqltips.com/tip.asp?tip=1653

SQL Server 2005: "Attempt to access expired blob handle"

FIX: Error message when you open a cursor for a very long cursor statement in SQL Server 2005: "Attempt to access expired blob handle"

SYMPTOMS
In Microsoft SQL Server 2005, you open a cursor for a very long cursor statement. For example, the cursor statement exceeds 65,536 bytes (64 kilobytes). When you perform an operation that clears the procedure cache, you may receive the following error message:
Msg 3624, Level 20, State 1, Line 2
A system assertion check has failed. Check the SQL Server error log for details. Typically, an assertion failure is caused by a software bug or data corruption. To check for database corruption, consider running DBCC CHECKDB. If you agreed to send dumps to Microsoft during setup, a mini dump will be sent to Microsoft. An update might be available from Microsoft in the latest Service Pack or in a QFE from Technical Support.
Msg 0, Level 20, State 0, Line 0
A severe error occurred on the current command. The results, if any, should be discarded.
Additionally, the following error message is logged in the SQL Server 2005 error log:
Date Time SPID * BEGIN STACK DUMP:
Date Time SPID * Date Time SPID
Date Time SPID *
Date Time SPID * Location: tmpilb.cpp:2628
Date Time SPID * Expression: fFalse
Date Time SPID * SPID: SPID
Date Time SPID * Process ID: ProcessID
Date Time SPID * Description: Attempt to access expired blob handle (3)
Date Time SPID *
Date Time SPID * Input Buffer 78 bytes -
Date Time SPID * dbcc freeproccache
This issue may occur if one of the following conditions is true:• You run the DBCC FREEPROCCACHE statement after you close the cursor.
• You run the DBCC FREEPROCCACHE statement, and then you close the cursor.
• You disconnect the connection without closing the cursor.

Microsoft SQL Server 2005 Standard Edition
Microsoft SQL Server 2005 Developer Edition
Microsoft SQL Server 2005 Enterprise Edition
Microsoft SQL Server 2005 Standard X64 Edition
Microsoft SQL Server 2005 Standard Edition for Itanium-based Systems
Microsoft SQL Server 2005 Enterprise X64 Edition
Microsoft SQL Server 2005 Enterprise Edition for Itanium-based Systems
Microsoft SQL Server 2005 Workgroup Edition

http://support.microsoft.com/kb/955686

Event ID 7000 appears in the System log while starting SQL 2005 Server

Event ID 7000 appears in the System log while starting SQL 2005 Server

SYMPTOMS

When you start a computer that is running Microsoft SQL 2005 Server, you may receive the following error message:

"At least one service or driver failed during system startup. Use Event Viewer to examine the event log for details."

Additionally, the following event appears in the System log in Event Viewer:

Event Source: Service Control Manager
Event Category: None
Event ID: 7000
Date: date
Time: time
Type: Error
User: N/A
Computer: computername
Description The SQL Server Reporting Services (MSSQLSERVER) service failed to start due to the following error:
The service did not respond to the start or control request in a timely fashion.


Note: The description may contain information on another SQL service name.

Microsoft SQL Server 2005 Standard Edition
Microsoft Windows Server 2003, Enterprise Edition (32-bit x86)
Microsoft Windows Server 2003, Datacenter Edition (32-bit x86)
Microsoft SQL Server 2005 Enterprise Edition
Microsoft Windows Server 2003, Standard Edition (32-bit x86)

http://support.microsoft.com/kb/558119

SET ANSI_NULLS ON / OFF

Mit SET ANSI_NULLS wird das Verhalten mit NULL-Werten festgelegt, wenn innerhalb einer WHERE-Bedingung die Vergleichsoperatoren „=“ und „<>“ verwendet werden.

SET ANSI_NULLS ON mit der Bedingung WHERE MiddleName = NULL liefert keine Datensätze zurück.

SET ANSI_NULLS ON
SELECT Title, FirstName, MiddleName
FROM   Person.Contact
WHERE MiddleName = NULL

Title    FirstName                     MiddleName
-------- ----------------------------- --------------------------------------------------

(0 row(s) affected)

Die Bedingung für die Ermittlung der Datensätze mit NULL-Werten kann wie folgt aussehen:

SET ANSI_NULLS ON
SELECT Title, FirstName, MiddleName
FROM   Person.Contact
WHERE MiddleName IS NULL

Title    FirstName                     MiddleName
-------- ----------------------------- --------------------------------------------------
Mr.      Gustavo                       NULL
Ms.      Kim                           NULL
Sr.      Humberto                      NULL
Sra.     Pilar                         NULL
Mr.      Jay                           NULL
Mr.      François                      NULL
Ms.      Kim                           NULL
Ms.      Michelle                      NULL
Mr.      Michael                       NULL
Mr.      John                          NULL
...
(8499 row(s) affected)

SET ANSI_NULLS OFF mit der Bedingung WHERE MiddleName = NULL liefert die gleichen Datensätze wie WHERE MiddleName IS NULL zurück.

SET ANSI_NULLS OFF
SELECT Title, FirstName, MiddleName
FROM   Person.Contact
WHERE MiddleName = NULL

Title    FirstName                     MiddleName
-------- ----------------------------- --------------------------------------------------
Mr.      Gustavo                       NULL
Ms.      Kim                           NULL
Sr.      Humberto                      NULL
Sra.     Pilar                         NULL
Mr.      Jay                           NULL
Mr.      François                      NULL
Ms.      Kim                           NULL
Ms.      Michelle                      NULL
Mr.      Michael                       NULL
Mr.      John                          NULL
...
(8499 row(s) affected)

In SQL-Server 2005-Dokumentation wird drauf hingewiesen, dass dieses Feature in einer zukünftigen Version von Microsoft SQL Server entfernt wird. Daher wird empfohlen, dieses Feature bei neuen Anwendungen nicht zu verwenden.