Script Database or Application Role

Leave a comment

I was surprised that SSMS does not allow the scripting of user defined database/application roles. Looked around the googlesphere and couldn’t find a script that would produce what my purpose required which is migration. The below script will provide you with the GRANT statement for your secured principles. All that remains, is a CREATE ROLE statement preceding the GRANT statements for the environment you’re migrating too. (CREATE ROLE BLUEBOOKapp [YourRoleName])

SELECT ‘GRANT ‘ + dp.permission_name + ‘ ON ‘ + OBJECT_NAME(major_id) + ‘ TO ‘ + rl.name + CHAR(13) COLLATE Latin1_General_CI_AS

FROM sys.database_permissions dp
JOIN sys.database_principals rl
ON dp.grantee_principal_id = rl.principal_id
WHERE rl.NAME = [YourRoleName]

Example of the T-SQL produced by the script.

Example of the T-SQL produced by the script.

Seek Predicates (Execution Plans)

Leave a comment

I was researching seek predicate information while working through an execution plan looking to increase query performance. Came across this great article by Craig Freedman that explains seek predicates how they function within single column indexes, multiple column indexes and also how functions muck things up a bit for Index seeks.

Craig Freedman’s SQL Server Blog – Seek Predicates

 

DBCC UPDATEUSAGE (Error 8989)

Leave a comment

I was greeted at work this morning with a wonderful SQL job failed email. The job in question failed with the below details:

“Executed as user: sqluser. The In-row data USED page count for object “sqltable“, index ID 8, partition ID 2387525817270272, alloc unit ID 2387525817270272 (type In-row data) is incorrect. Run DBCC UPDATEUSAGE. [SQLSTATE 42000] (Error 2508) The In-row data RSVD page count for object “sqltable“, index ID 8, partition ID 2387525817270272, alloc unit ID 2387525817270272 (type In-row data) is incorrect. Run DBCC UPDATEUSAGE. [SQLSTATE 42000] (Error 2508) CHECKDB found 0 allocation errors and 2 consistency errors in table ‘sqltable2‘ (object ID 2071014459). [SQLSTATE 01000] (Error 8990) CHECKDB found 0 allocation errors and 2 consistency errors in database ‘databasename‘. [SQLSTATE 01000] (Error 8989). The step failed.”

For the above issue I have a very concise answer found by way of msdn Books Online. 🙂

Reports and corrects pages and row count inaccuracies in the catalog views. These inaccuracies may cause incorrect space usage reports returned by the sp_spaceused system stored procedure.

For further detail follow the below link to the msdn library for a full description and usage of the management command, DBCC UPDATEUSAGE.

MSDN – DBCC UPDATEUSAGE

Parse Path from Filename in the Catalog.Path table in Report Server.

Leave a comment

On my trials and tribs trying to develop a report to display SQL Report Server execution data (from a SRS server in Sharepoint integration mode) I found a way to parse the pathname from the Catalog.Path column in the Catalog table. (see below)

 

I left the stages of the parse routine commented out for your own research. To fill in the variables query your report server’s catalog table. There you should find valid path names and to query against. Heck, also comment out the @Report and @User variables unless you also plan to filter

by those two criteria.

Clean formatting 🙂

 

Messy Script

DECLARE @Report VARCHAR(50) = ‘Adhoc Analysis Report.rdl’
DECLARE @User VARCHAR(50) = ‘CORP\sa_aforney’
DECLARE @Path VARCHAR(50) = ‘/LIMS/Reports/Job Logs/’

SELECT ExecutionLogStorage.ReportID
,ExecutionLogStorage.UserName
,ExecutionLogStorage.TimeStart
,ExecutionLogStorage.TimeEnd
,ExecutionLogStorage.TimeDataRetrieval
,ExecutionLogStorage.TimeProcessing
,ExecutionLogStorage.TimeRendering
,[Catalog].ItemID
,[Catalog].[Path]
–,SUBSTRING([Catalog].[Path], 40, 100) AS [1]
–,REVERSE(SUBSTRING([Catalog].[Path], 40, 100)) AS [2]
–,CHARINDEX(‘/’, REVERSE(SUBSTRING([Catalog].[Path], 40, 100))) AS [3]
–,SUBSTRING(REVERSE(SUBSTRING([Catalog].[Path], 40, 100)),CHARINDEX(‘/’, REVERSE(SUBSTRING([Catalog].[Path], 40, 100))),100) AS [4]
,REVERSE(SUBSTRING(REVERSE(SUBSTRING([Catalog].[Path], 40, 100)),CHARINDEX(‘/’, REVERSE(SUBSTRING([Catalog].[Path], 40, 100))),100)) AS [AbrvPath]
,LEN([Catalog].[Path])
,[Catalog].Name
,[Catalog].ParentID
,[Catalog].Type
,[Catalog].Description
FROM ExecutionLogStorage
INNER JOIN [Catalog]
ON ExecutionLogStorage.ReportID = [Catalog].ItemID
WHERE [Catalog].Name <> ‘ ‘
–AND [Catalog].Name IN ( @Report )
AND ExecutionLogStorage.UserName IN ( @User )
AND REVERSE(SUBSTRING(REVERSE(SUBSTRING([Catalog].[Path], 40, 100)),CHARINDEX(‘/’, REVERSE(SUBSTRING([Catalog].[Path], 40, 100))),100)) IN (@Path)
ORDER BY ExecutionLogStorage.TimeStart DESC

SQL Triggers

Leave a comment

Below trigger includes the exclusion of the @@ROWCOUNT system function due to issues with the MERGE statement and erratic behavior.

SQL Code

 

System Views – Database Extended Properties

Leave a comment

This clever little guy will grab most of the extended database properties from within sys.databases for each database for the server the query is run against.  The schema scope of extended properties (column metadata) will be included in a later post.

Took me a while to code the Case Statements but Here ya go!

CREATE TABLE #temp
(
db_name SYSNAME
,name SYSNAME
,value SQL_VARIANT
)

DECLARE @command VARCHAR(1000)
SELECT @command = ‘USE [?] insert into #temp select db_name(), name, value from sys.extended_properties where class = 0 ‘
EXEC sp_MSforeachdb @command

SELECT #temp.db_name
,#temp.value
,sys.databases.name
,sys.databases.create_date
,sys.databases.compatibility_level
,sys.databases.collation_name
,sys.databases.user_access_desc
,CASE WHEN sys.databases.is_read_only = 0 THEN ‘Read Only’
WHEN is_read_only = 1 THEN ‘Read Write’
END AS is_read_only
,CASE WHEN sys.databases.is_auto_close_on = 1 THEN ‘On’
WHEN sys.databases.is_auto_close_on = 0 THEN ‘Off’
END AS is_auto_close_on
,CASE WHEN sys.databases.is_auto_shrink_on = 1 THEN ‘On’
WHEN sys.databases.is_auto_shrink_on = 0 THEN ‘Off’
END AS is_auto_shrink_on
,sys.databases.state_desc
,CASE WHEN sys.databases.is_cleanly_shutdown = 1 THEN ‘On’
WHEN sys.databases.is_cleanly_shutdown = 0 THEN ‘Off’
END AS is_cleanly_shutdown
,CASE WHEN sys.databases.is_supplemental_logging_enabled = 1 THEN ‘On’
WHEN sys.databases.is_supplemental_logging_enabled = 0 THEN ‘Off’
END AS is_supplemental_logging_enabled
,sys.databases.snapshot_isolation_state_desc
,CASE WHEN sys.databases.is_read_committed_snapshot_on = 1 THEN ‘On’
WHEN sys.databases.is_read_committed_snapshot_on = 0 THEN ‘Off’
END AS is_read_committed_snapshot_on
,sys.databases.recovery_model_desc
,sys.databases.page_verify_option_desc
,CASE WHEN sys.databases.is_auto_create_stats_on = 1 THEN ‘On’
WHEN sys.databases.is_auto_create_stats_on = 0 THEN ‘Off’
END AS is_auto_create_stats_on
,CASE WHEN sys.databases.is_auto_update_stats_async_on = 1 THEN ‘On’
WHEN sys.databases.is_auto_update_stats_async_on = 0 THEN ‘Off’
END AS is_auto_update_stats_async_on
,CASE WHEN sys.databases.is_ansi_null_default_on = 1 THEN ‘On’
WHEN sys.databases.is_ansi_null_default_on = 0 THEN ‘Off’
END AS is_ansi_null_default_on
,CASE WHEN sys.databases.is_ansi_nulls_on = 1 THEN ‘On’
WHEN sys.databases.is_ansi_nulls_on = 0 THEN ‘Off’
END AS is_ansi_nulls_on
,CASE WHEN sys.databases.is_ansi_padding_on = 1 THEN ‘On’
WHEN sys.databases.is_ansi_padding_on = 0 THEN ‘Off’
END AS is_ansi_padding_on
,CASE WHEN sys.databases.is_ansi_warnings_on = 1 THEN ‘On’
WHEN sys.databases.is_ansi_warnings_on = 0 THEN ‘Off’
END AS is_ansi_warnings_on
,CASE WHEN sys.databases.is_arithabort_on = 1 THEN ‘On’
WHEN sys.databases.is_arithabort_on = 0 THEN ‘Off’
END AS is_arithabort_on
,CASE WHEN sys.databases.is_concat_null_yields_null_on = 1 THEN ‘On’
WHEN sys.databases.is_concat_null_yields_null_on = 0 THEN ‘Off’
END AS is_concat_null_yields_null_on
,CASE WHEN sys.databases.is_numeric_roundabort_on = 1 THEN ‘On’
WHEN sys.databases.is_numeric_roundabort_on = 0 THEN ‘Off’
END AS is_numeric_roundabort_on
,CASE WHEN sys.databases.is_quoted_identifier_on = 1 THEN ‘On’
WHEN sys.databases.is_quoted_identifier_on = 0 THEN ‘Off’
END AS is_quoted_identifier_on
,CASE WHEN sys.databases.is_recursive_triggers_on = 1 THEN ‘On’
WHEN sys.databases.is_recursive_triggers_on = 0 THEN ‘Off’
END AS is_recursive_triggers_on
,CASE WHEN sys.databases.is_cursor_close_on_commit_on = 1 THEN ‘On’
WHEN sys.databases.is_cursor_close_on_commit_on = 0 THEN ‘Off’
END AS is_cursor_close_on_commit_on
,CASE WHEN sys.databases.is_local_cursor_default = 1 THEN ‘Local’
WHEN sys.databases.is_local_cursor_default = 0 THEN ‘Global’
END AS is_local_cursor_default
,CASE WHEN sys.databases.is_fulltext_enabled = 1 THEN ‘On’
WHEN sys.databases.is_fulltext_enabled = 0 THEN ‘Off’
END AS is_fulltext_enabled
,CASE WHEN sys.databases.is_trustworthy_on = 1 THEN ‘On’
WHEN sys.databases.is_trustworthy_on = 0 THEN ‘Off’
END AS is_trustworthy_on
,CASE WHEN sys.databases.is_db_chaining_on = 1 THEN ‘On’
WHEN sys.databases.is_db_chaining_on = 0 THEN ‘Off’
END AS is_db_chaining_on
,CASE WHEN sys.databases.is_parameterization_forced = 1 THEN ‘FORCED’
WHEN sys.databases.is_parameterization_forced = 0 THEN ‘SIMPLE’
END AS is_parameterization_forced
,CASE WHEN sys.databases.is_master_key_encrypted_by_server = 1 THEN ‘On’
WHEN sys.databases.is_master_key_encrypted_by_server = 0 THEN ‘Off’
END AS is_master_key_encrypted_by_server
,CASE WHEN sys.databases.is_published = 1 THEN ‘On’
WHEN sys.databases.is_published = 0 THEN ‘Off’
END AS is_published
,CASE WHEN sys.databases.is_subscribed = 1 THEN ‘On’
WHEN sys.databases.is_subscribed = 0 THEN ‘Off’
END AS is_subscribed
,CASE WHEN sys.databases.is_merge_published = 1 THEN ‘On’
WHEN sys.databases.is_merge_published = 0 THEN ‘Off’
END AS is_merge_published
,CASE WHEN sys.databases.is_distributor = 1 THEN ‘On’
WHEN sys.databases.is_distributor = 0 THEN ‘Off’
END AS is_distributor
,CASE WHEN sys.databases.is_sync_with_backup = 1 THEN ‘On’
WHEN sys.databases.is_sync_with_backup = 0 THEN ‘Off’
END AS is_sync_with_backup
,sys.databases.service_broker_guid
,CASE WHEN sys.databases.is_broker_enabled = 1 THEN ‘Yes’
WHEN sys.databases.is_broker_enabled = 0 THEN ‘No’
END AS is_broker_enabled
,sys.databases.log_reuse_wait_desc
,CASE WHEN sys.databases.is_date_correlation_on = 1 THEN ‘Yes’
WHEN sys.databases.is_date_correlation_on = 0 THEN ‘No’
END AS is_date_correlation_on
,CASE WHEN sys.databases.is_cdc_enabled = 1 THEN ‘Yes’
WHEN sys.databases.is_cdc_enabled = 0 THEN ‘No’
END AS is_cdc_enabled
,CASE WHEN sys.databases.is_encrypted = 1 THEN ‘Encrypted’
WHEN sys.databases.is_encrypted = 0 THEN ‘Not Encrypted’
END AS is_encrypted
,CASE WHEN sys.databases.is_honor_broker_priority_on = 1 THEN ‘On’
WHEN sys.databases.is_honor_broker_priority_on = 0 THEN ‘Off’
END AS is_honor_broker_priority_on
FROM #temp
RIGHT OUTER JOIN sys.databases
ON #temp.db_name = sys.databases.name
DROP TABLE #temp

SQL Server 2008 – System View Mapping

Leave a comment

SQL Server 2008 – System View Mapping

http://www.microsoft.com/download/en/details.aspx?displaylang=en&id=9301

%d bloggers like this: