Archive for the ‘T-SQL’ Category

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 […]

A quick example of why IN can be bad

Here’s just a quick demo that illustrates why the IN operator in TSQL might not perform as well as alternatives (like a range query or joining onto a temporary table containing your values). ?View Code TSQLIF OBJECT_ID(’tempdb..#temp’) IS NOT NULL BEGIN DROP TABLE #temp; END;   CREATE TABLE #temp ( id INTEGER NOT NULL IDENTITY(1,1), […]

Generate PK drops and creates using TSQL

Here’s just a couple of queries I used to generate PK drops and creates using the sys.key_constraints view. I wanted to do this for a database using Poor Mans Partitioning. Generate drops… ?View Code TSQLSELECT ‘ALTER TABLE ‘ + OBJECT_SCHEMA_NAME(t.[object_id]) + ‘.’ + t.name + ‘ DROP CONSTRAINT ‘ + c.[name] + ‘;’ FROM sys.key_constraints […]

SQL Server Partitioning for Paupers

Last year I posted about a pauper’s partitioning technique I used with SQL Server to solve some data purging issues. In a similar vein I recently found SQL Server partitioning without Enterprise Edition that looks like the answer to an issue in our systems. We have large amounts of data but simply can’t justify the cost […]