Find all permissions for all users in a database

WITH cteUserPermissions
AS
(
-- Permissions provisioned to a sql user
-- or windows user/group directly
SELECT  
        [UserName] = CASE DP.[type]
                                        WHEN 'S' THEN DP.[name]
                                        WHEN 'U' THEN L.[name]
                                 END,
        [UserType] = CASE DP.[type]
                                        WHEN 'S' THEN 'SQL User'
                                        WHEN 'U' THEN 'Windows User'
                                 END,  
        [DatabaseUserName] = DP.[name],      
        [Role] = null,      
        [PermissionState] = P.[state_desc],
        [PermissionName] = P.permission_name,
        [ObjectName] = OBJECT_NAME(P.major_id),
        [ObjectType] = O.type_desc    
FROM        
        sys.database_principals DP
        LEFT JOIN sys.login_token L ON DP.[sid] = L.[sid]
        LEFT JOIN sys.database_permissions P ON P.[grantee_principal_id] = DP.[principal_id]
        LEFT JOIN sys.objects O ON P.[major_id] = O.[object_id]
WHERE DP.[type] in ('S','U')  

UNION

-- Permissions provisioned to a sql user or windows user/group
-- through a database or application role
SELECT  
        [UserName] = CASE M.[type]
                                        WHEN 'S' THEN M.[name]
                                        WHEN 'U' THEN L.[name]
                                 END,
        [UserType] = CASE M.[type]
                                        WHEN 'S' THEN 'SQL User'
                                        WHEN 'U' THEN 'Windows User'
                                 END,
        [DatabaseUserName] = M.[name],  
        [Role] = R.[name],      
        [PermissionState] = P.[state_desc],
        [PermissionName] = P.permission_name,
        [ObjectName] = OBJECT_NAME(P.major_id),
        [ObjectType] = O.type_desc
   
FROM        
        sys.database_role_members DRM
        JOIN sys.database_principals R ON R.[principal_id] = DRM.[role_principal_id]
        JOIN sys.database_principals M ON M.[principal_id] = DRM.[member_principal_id]
        LEFT JOIN sys.login_token L ON M.[sid] = L.[sid]
        LEFT JOIN sys.database_permissions P ON P.[grantee_principal_id] = R.[principal_id]
        LEFT JOIN sys.objects O ON P.[major_id] = O.[object_id]

UNION

-- Permissions provisioned to the public role,
-- which everyone gets by default
SELECT  
        [UserName] = '{All Users}',
        [UserType] = '{All Users}',
        [DatabaseUserName] = '{All Users}',      
        [Role] = DP.[name],      
        [PermissionState] = P.[state_desc],
        [PermissionName] = P.permission_name,
        [ObjectName] = OBJECT_NAME(P.major_id),
        [ObjectType] = O.type_desc
FROM
        sys.database_principals DP
        LEFT JOIN sys.database_permissions P ON P.[grantee_principal_id] = DP.[principal_id]
        JOIN sys.objects O ON O.[object_id] = P.[major_id]
WHERE
        DP.[type] = 'R'                         -- only roles
        AND DP.[name] = 'public'        -- only public role
        AND O.is_ms_shipped = 0
)
SELECT *
FROM cteUserPermissions
WHERE UserName in ('{All Users}', 'myusername')
ORDER BY UserName, ObjectName, PermissionState, PermissionName

Upsert and Merge with SSIS

Question
Check if data exists in the destination table then update it, otherwise insert new record.

Solution using Lookup
- on match you execute an UPDATE statement via **OLE DB command**
- on error you add row in destination via **OLE DB destination task**

More details: here.

upsert with sql server integration services

Solution using Merge Join Transformation and Conditional Split
More details: here.
upsert with sql server integration services

Comparing table structure with TSQL

Compare table structures using sys.columns:

DECLARE
        @table1 as NVARCHAR(255) = N'Reports',
        @table2 as NVARCHAR(255) = N'Reports_Archives'

;WITH cteColumns
AS
(
        SELECT C.object_id, C.name as column_name, T.name as type_name, C.max_length, C.precision
        FROM
                sys.columns C
                INNER JOIN sys.types T ON T.system_type_id = C.system_type_id
)
SELECT column_name, type_name, max_length, precision, 'In ' + @table1
FROM cteColumns
WHERE object_id = OBJECT_ID(@table1)
EXCEPT
SELECT column_name, type_name, max_length, precision, 'In ' + @table1
FROM cteColumns
WHERE object_id = OBJECT_ID(@table2)
 
UNION ALL
 
SELECT column_name, type_name, max_length, precision, 'In ' + @table2
FROM cteColumns
WHERE object_id = OBJECT_ID(@table2)
EXCEPT
SELECT column_name, type_name, max_length, precision, 'In ' + @table2
FROM cteColumns
WHERE object_id = OBJECT_ID(@table1)

When statistics were last updated in SQL Server?

T-SQL showing when statistics were last updated using STATS_DATE function in SQL Server:

SELECT
        OBJECT_NAME(S.object_id) as object_name,  S.name as stat_name,
        S.auto_created, S.user_created, S.no_recompute,
        STATS_DATE(S.object_id, stats_id) as last_updated
FROM
        sys.stats S
        INNER JOIN sys.objects O ON O.object_id = S.object_id AND O.is_ms_shipped = 0
ORDER BY object_name

How to (manually) update the statistics?

  • Force stats to update for a specific table (including clause WITH FULLSCAN)
    UPDATE STATISTICS <tablename>;
  • Full sampling:
    UPDATE STATISTICS <tableName> (<columnName>) WITH FULLSCAN;
  • Force stats to update for the entire database:
    EXEC sp_updatestats;

Identify blocking in SQL Server

Run this T-SQL statement to identify current blockings in the current database:

SELECT *
FROM sys.sysprocesses
WHERE
        dbid = DB_ID()
        AND blocked > 0

Too see T-SQL that blocks, just take the value of spid column returned above and run the following command:

DBCC INPUTBUFFER(<spid>);

In order to see the blocked T-SQL command, take the value of column returned above and run:

DBCC INPUTBUFFER(<blocked>);

Find missing index warnings in cached query plans

Find missing index warnings for cached plans in the current database:

SELECT TOP 20
        OBJECT_NAME(objectid) as object_name,
        CP.objtype, CP.usecounts,
        QP.query_plan  
FROM
        sys.dm_exec_cached_plans CP
        CROSS APPLY sys.dm_exec_query_plan(CP.plan_handle) QP
WHERE
        CAST(QP.query_plan as NVARCHAR(MAX)) LIKE N'%MissingIndex%'
        AND dbid = DB_ID()
ORDER BY CP.usecounts DESC

Count of SQL Server connections by IP address

SELECT
        EC.client_net_address, ES.host_name, ES.program_name, ES.login_name,
        COUNT (ec.session_id) as NoConnections
FROM
        sys.dm_exec_sessions ES
        INNER JOIN sys.dm_exec_connections EC ON ES.session_id = EC.session_id
GROUP BY EC.client_net_address, ES.program_name, ES.host_name, ES.login_name
ORDER BY NoConnections DESC

Creating readonly views in Sql Server

According to MSDN, views composed of simple selects automatically allow you to use insert/update/delete statements on the table.

There are several different options for avoiding this and in this article I will present various ways to make a view read only in a SQL Server database:

1. Permissions
You have the option to remove UPDATE/DELETE/INSERT permissions on the view.

2. UsingINSTEAD OFtrigger

CREATE TRIGGER dbo.MySampleView_Trigger_OnInsertOrUpdateOrDelete]
ON dbo.MySampleView
INSTEAD OF INSERT, UPDATE, DELETE
AS
BEGIN
        RAISERROR ('You are not allow to update this view!', 16, 1)    
END

3. UsingUNIONoperator
You could specify an UNION operator in order to make SQL Server fail during the INSERT/UPDATE/DELETE operation. See the below example:

ALTER VIEW dbo.MySampleView
as
SELECT col1, col2 FROM dbo.MySampleTable
UNION
SELECT NULL, NULL WHERE 1 =0

Remove duplicates

De-duplication of data is a common need, especially when dealing with data-quality issues in environments that end up with duplicate rows due to lack of enforcement of uniqueness with constraints. As an example, the following code prepares sample data with duplicate orders in a table called MyItem:

IF EXISTS (SELECT 1 FROM sys.tables WHERE name = 'MyItems')
        DROP TABLE dbo.MyItems
GO
               
               
CREATE TABLE dbo.MyItems
(
        IdItem INT IDENTITY(1,1),
        Name VARCHAR(50),
        Description VARCHAR(256)
)
GO

INSERT INTO dbo.MyItems(Name, Description)
VALUES
        ('Item1', 'Description 1'),
        ('Item1', 'Description 1'),
        ('Item2', 'Description 2'),
        ('Item3', 'Description 3'),
        ('Item3', 'Description 3'),
        ('Item3', 'Description 3 - different'),
        ('Item4', 'Description 4')
       
SELECT *
FROM dbo.MyItems
GO

An option is to filter either just the distinct rows or all but the distinct rows. You compute both ROW_NUMBER and RANK based on Name + Description ordering, like so:

SELECT
        IdItem, Name, Description,
        ROW_NUMBER() OVER (ORDER BY Name, Description) as row_num,
        RANK() OVER (ORDER BY Name, Description) as rnk
FROM dbo.MyItems
GO

Suppose that you need to de-duplicate the data, keeping only one occurrence of each unique Name+Description combination.

WITH cte
AS
(
        SELECT
                IdItem, Name, Description,
                ROW_NUMBER() OVER (ORDER BY Name, Description) as row_num,
                RANK() OVER (ORDER BY Name, Description) as rnk
        FROM dbo.MyItems
)
DELETE FROM cte
WHERE row_num != rnk

SELECT IdItem, Name, Description
FROM dbo.MyItems

When you’re done, run the following code for cleanup:

IF EXISTS (SELECT 1 FROM sys.tables WHERE name = 'MyItems')
        DROP TABLE dbo.MyItems
GO

Pages

Subscribe to SQLhint RSS