Archive for the ‘T-SQL’ Category

TSQL: Accuracy of DATETIME

Here’s something I didn’t know about the DATETIME data type in SQL Server…. ?View Code TSQLSELECT CAST(’2014-04-10 00:00:00.000′ AS DATETIME) UNION ALL SELECT CAST(’2014-04-10 00:00:00.001′ AS DATETIME) UNION ALL SELECT CAST(’2014-04-10 00:00:00.002′ AS DATETIME) UNION ALL SELECT CAST(’2014-04-10 00:00:00.003′ AS DATETIME) UNION ALL SELECT CAST(’2014-04-10 00:00:00.004′ AS DATETIME) UNION ALL SELECT CAST(’2014-04-10 00:00:00.005′ AS DATETIME) […]

TSQL: Table count per filegroup

Here’s a query that uses the SQL Server System Catalog Views to return a table count per table. I used this to check even table distribution in a data warehouse. ?View Code TSQLSELECT ds.name AS filegroup_name, COUNT(DISTINCT t.[object_id]) AS table_count FROM sys.tables t INNER JOIN sys.indexes i ON t.[object_id] = i.[object_id] AND i.is_primary_key = 1 INNER […]

The database cannot be recovered because the log was not restored

A test restore of a SQL Server database had somehow been left in the “RESTORING” state. I attempted to bring the database online with ?View Code TSQLRESTORE DATABASE db_name; But I was greeted with… Msg 4333, Level 16, State 1, Line 1 The database cannot be recovered because the log was not restored. Msg 3013, […]

What permissions have your users really got?

Here’s a TSQL script to audit the permissions of certain AD users access to a SQL Server instance. This script uses the EXECUTE AS LOGIN clause and the system function sys.fn_my_permissions. All databases on the SQL Server instance are queried and the script will output results containing the assigned user permissions. To get started all […]

Audit database user & role mappings in SQL Server

This script provides you with a list, of the database user and database role mappings, for an entire SQL Server instance. The following system views are used; sys.database_role_members sys.database_principals ?View Code TSQLCREATE TABLE #user_groups ( username VARCHAR(50), [login_type] VARCHAR(50), database_name VARCHAR(50), database_group VARCHAR(50) );     EXECUTE sp_MSForEachDB ‘USE ?; INSERT INTO #user_groups SELECT dp.name […]