Thursday, September 17, 2009

Stopping a Script with Embedded GO Statements

I’m sure you’ve run into this issue before: you have a script that needs GO statements, but you want to stop running if there’s an error.
PRINT 'Before 19'
GO
RAISERROR
('Fatal error', 19, 1) WITH LOG
RETURN
GO
PRINT
'After  19'
GO

PRINT
'Before 20'
GO
RAISERROR
('Fatal error', 20, 1) WITH LOG
RETURN
GO
PRINT
'After  20'
GO



Before 19
Msg 50000, Level 19, State 1, Line 1
Fatal error
After 19 -- It kept going after RAISERROR/RETURN
Before 20
Msg 2745, Level 16, State 2, Line 1
Process ID 110 has raised user error 50000, severity 20. SQL Server is terminating this process.
Msg 50000, Level 20, State 1, Line 1
Fatal error
Msg 0, Level 20, State 0, Line 0
A severe error occurred on the current command. The results, if any, should be discarded.
Bailing out requires both a severity of 20 or higher and the “WITH LOG” option and running with special permissions.

Notes from BOL:
  • “Only a member of the sysadmin fixed server role or a user with ALTER TRACE permissions can specify WITH LOG.”
  • “Severity levels from 19 through 25 can only be specified by members of the sysadmin fixed server role or users with ALTER TRACE permissions. For severity levels from 19 through 25, the WITH LOG option is required.”
  • “Severity levels from 20 through 25 are considered fatal. If a fatal severity level is encountered, the client connection is terminated after receiving the message, and the error is logged in the error and application logs.”

Tuesday, September 01, 2009

Find All Public Role Permissions

A standard best-practice in SQL Server is that the public role should not have any permissions. Here is a script that will show you all permissions granted to the public role in all databases on an instance.
-- Find all permissions in all databases granted to the public role
SET NOCOUNT ON
GO

IF OBJECT_ID('tempdb..#DBList') IS NOT NULL
DROP TABLE #DBList
IF OBJECT_ID('tempdb..#ObjectList') IS NOT NULL
DROP TABLE #ObjectList

CREATE TABLE #DBList (
EntryID int IDENTITY,
DbName sysname
)

CREATE TABLE #ObjectList (
EntryID int IDENTITY PRIMARY KEY,
DatabaseName nvarchar(128),
ObjectType nvarchar(60),
ObjectName sysname,
Permission nvarchar(128),
GrantState nvarchar(60)
)

DECLARE @dbCt int
DECLARE @objCt int
DECLARE @dbName nvarchar(128)
DECLARE @sql nvarchar(4000)

INSERT INTO #DBList (
DbName
)
SELECT name
FROM sys.databases
ORDER BY name

SET @dbCt = 0
WHILE @dbCt < (SELECT COUNT(*) FROM #DBList) BEGIN
SET @dbCt = @dbCt + 1
SELECT @dbName = DbName
FROM #DBList
WHERE EntryID = @dbCt

SET @sql =
N'USE ' + QUOTENAME(@dbName) + ';
INSERT INTO #ObjectList (
DatabaseName,
ObjectType,
ObjectName,
Permission,
GrantState
)
SELECT
DB_NAME(),
o.type_desc,
o.name,
dp.permission_name,
dp.state_desc
FROM sys.database_permissions AS dp
JOIN sys.objects AS o ON dp.major_id = o.[object_id]
WHERE 1 = 1
AND dp.grantee_principal_id = (
SELECT principal_id
FROM sys.database_principals
WHERE name = ''public''
)
AND dp.[state] IN (''G'', ''W'')
AND o.is_ms_shipped = 0
AND o.name NOT LIKE ''MSmerge%''
AND o.name NOT LIKE ''sysmerge%''
AND o.name NOT IN (
''MSsubscription_properties''
)
ORDER BY
o.type_desc,
o.name,
dp.permission_name'

EXEC sp_executesql
@stmt = @sql,
@params = N'@dbNameParam nvarchar(128)',
@dbNameParam = @dbName;
END

SELECT
ROW_NUMBER() OVER (
PARTITION BY DatabaseName
ORDER BY EntryID
) AS [DB Row],
DatabaseName,
ObjectType,
ObjectName,
Permission,
GrantState
FROM #ObjectList
ORDER BY
EntryID
GO