2 SQL Server query to find all permissions/access for all users in a database

SELECT
        [UserType] = CASE princ.[type]
        WHEN 'S' THEN 'SQL User'
        WHEN 'U' THEN 'Windows User'
        WHEN 'G' THEN 'Windows Group'
        END,
        [DatabaseUserName] = princ.[name],
        [LoginName]        = ulogin.[name],
        [Role]             = NULL,
        [PermissionType]   = perm.[permission_name],
        [PermissionState]  = perm.[state_desc],
        [ObjectType] = CASE perm.[class]
        WHEN 1 THEN obj.[type_desc]        -- Schema-contained objects
        ELSE perm.[class_desc]             -- Higher-level objects
         END,
        [Schema] = objschem.[name],
        [ObjectName] = CASE perm.[class]
        WHEN 3 THEN permschem.[name]       -- Schemas
        WHEN 4 THEN imp.[name]             -- Impersonations
         ELSE OBJECT_NAME(perm.[major_id])  -- General objects
         END,
        [ColumnName] = col.[name]
    FROM
        --Database user 
sys.database_principals            AS princ
        --Login accounts
        LEFT JOIN sys.server_principals    AS ulogin    ON ulogin.[sid] = princ.[sid]
        --Permissions
        LEFT JOIN sys.database_permissions AS perm      ON perm.[grantee_principal_id] = princ.[principal_id]
        LEFT JOIN sys.schemas              AS permschem ON permschem.[schema_id] = perm.[major_id]
        LEFT JOIN sys.objects              AS obj       ON obj.[object_id] = perm.[major_id]
        LEFT JOIN sys.schemas              AS objschem  ON objschem.[schema_id] = obj.[schema_id]
        --Table columns
        LEFT JOIN sys.columns              AS col       ON col.[object_id] = perm.[major_id]
                                                           AND col.[column_id] = perm.[minor_id]
        --Impersonations
        LEFT JOIN sys.database_principals  AS imp       ON imp.[principal_id] = perm.[major_id]
    WHERE
        princ.[type] IN ('S','U','G')
        -- No need for these system accounts
        AND princ.[name] NOT IN ('sys', 'INFORMATION_SCHEMA')

UNION

    --2) List all access provisioned to a SQL user or Windows user/group through a database or application role
    SELECT
        [UserType] = CASE membprinc.[type]
                         WHEN 'S' THEN 'SQL User'
                         WHEN 'U' THEN 'Windows User'
                         WHEN 'G' THEN 'Windows Group'
                     END,
        [DatabaseUserName] = membprinc.[name],
        [LoginName]        = ulogin.[name],
        [Role]             = roleprinc.[name],
        [PermissionType]   = perm.[permission_name],
        [PermissionState]  = perm.[state_desc],
        [ObjectType] = CASE perm.[class]
                           WHEN 1 THEN obj.[type_desc]        -- Schema-contained objects
                           ELSE perm.[class_desc]             -- Higher-level objects
                       END,
        [Schema] = objschem.[name],
        [ObjectName] = CASE perm.[class]
                           WHEN 3 THEN permschem.[name]       -- Schemas
                           WHEN 4 THEN imp.[name]             -- Impersonations
                           ELSE OBJECT_NAME(perm.[major_id])  -- General objects
                       END,
        [ColumnName] = col.[name]
    FROM
        --Role/member associations
        sys.database_role_members          AS members
        --Roles
        JOIN      sys.database_principals  AS roleprinc ON roleprinc.[principal_id] = members.[role_principal_id]
        --Role members (database users)
        JOIN      sys.database_principals  AS membprinc ON membprinc.[principal_id] = members.[member_principal_id]
        --Login accounts
        LEFT JOIN sys.server_principals    AS ulogin    ON ulogin.[sid] = membprinc.[sid]
        --Permissions
        LEFT JOIN sys.database_permissions AS perm      ON perm.[grantee_principal_id] = roleprinc.[principal_id]
        LEFT JOIN sys.schemas              AS permschem ON permschem.[schema_id] = perm.[major_id]
        LEFT JOIN sys.objects              AS obj       ON obj.[object_id] = perm.[major_id]
        LEFT JOIN sys.schemas              AS objschem  ON objschem.[schema_id] = obj.[schema_id]
        --Table columns
        LEFT JOIN sys.columns              AS col       ON col.[object_id] = perm.[major_id]
                                                           AND col.[column_id] = perm.[minor_id]
        --Impersonations
        LEFT JOIN sys.database_principals  AS imp       ON imp.[principal_id] = perm.[major_id]
    WHERE
        membprinc.[type] IN ('S','U','G')
        -- No need for these system accounts
        AND membprinc.[name] NOT IN ('sys', 'INFORMATION_SCHEMA')

UNION

    --3) List all access provisioned to the public role, which everyone gets by default
    SELECT
        [UserType]         = '{All Users}',
        [DatabaseUserName] = '{All Users}',
        [LoginName]        = '{All Users}',
        [Role]             = roleprinc.[name],
        [PermissionType]   = perm.[permission_name],
        [PermissionState]  = perm.[state_desc],
        [ObjectType] = CASE perm.[class]
                           WHEN 1 THEN obj.[type_desc]        -- Schema-contained objects
                           ELSE perm.[class_desc]             -- Higher-level objects
                       END,
        [Schema] = objschem.[name],
        [ObjectName] = CASE perm.[class]
                           WHEN 3 THEN permschem.[name]       -- Schemas
                           WHEN 4 THEN imp.[name]             -- Impersonations
                           ELSE OBJECT_NAME(perm.[major_id])  -- General objects
                       END,
        [ColumnName] = col.[name]
    FROM
        --Roles
        sys.database_principals            AS roleprinc
        --Role permissions
        LEFT JOIN sys.database_permissions AS perm      ON perm.[grantee_principal_id] = roleprinc.[principal_id]
        LEFT JOIN sys.schemas              AS permschem ON permschem.[schema_id] = perm.[major_id]
        --All objects
        JOIN      sys.objects              AS obj       ON obj.[object_id] = perm.[major_id]
        LEFT JOIN sys.schemas              AS objschem  ON objschem.[schema_id] = obj.[schema_id]
        --Table columns
        LEFT JOIN sys.columns              AS col       ON col.[object_id] = perm.[major_id]
                                                           AND col.[column_id] = perm.[minor_id]
        --Impersonations
        LEFT JOIN sys.database_principals  AS imp       ON imp.[principal_id] = perm.[major_id]
    WHERE
        roleprinc.[type] = 'R'
        AND roleprinc.[name] = 'public'
        AND obj.[is_ms_shipped] = 0

ORDER BY
    [UserType],
    [DatabaseUserName],
    [LoginName],
    [Role],
    [Schema],
    [ObjectName],
    [ColumnName],
    [PermissionType],
    [PermissionState],
    [ObjectType]

Script to check Link server name in View and Store Procedure

--Script to check Link server name in View / SP SELECT

Distinct

referenced_Server_name As LinkedServerName,

referenced_schema_name AS LinkedServerSchema,

referenced_database_name AS LinkedServerDB,

referenced_entity_name As LinkedServerTable,

OBJECT_NAME (referencing_id) AS ObjectUsingLinkedServer

FROM sys.sql_expression_dependencies

WHERE referenced_database_name IS NOT NULL

And referenced_Server_name in ('YourLinkServerName') ---Script to check Link server in SQL Server 2005 SELECT OBJECT_NAME(object_id), * FROM sys.sql_modules WHERE definition LIKE '%YourLinkServerName%'

Script to check Disk IO

select db_name(database_id) as DatabaseName, file_id,io_stall_read_ms,num_of_reads
,cast(io_stall_read_ms/(1.0+num_of_reads) as numeric(10,1)) as 'avg_read_stall_ms'
,io_stall_write_ms,num_of_writes,cast(io_stall_write_ms/(1.0+num_of_writes) as numeric(10,1)) as 'avg_write_stall_ms',
io_stall_read_ms + io_stall_write_ms as io_stalls,num_of_reads + num_of_writes as total_io,cast((io_stall_read_ms+io_stall_write_ms)/(1.0+num_of_reads + num_of_writes) as numeric(10,1)) as 'avg_io_stall_ms'
from sys.dm_io_virtual_file_stats(null,null)
order by [DatabaseName] desc

Monitoring SQL Server Performance using Query Store

Query Store is a new functionality introduced since SQL Server 2016, I really love this.

What is Query Store: SQL Server Query Store feature provides you with insight on query plan choice and performance. It simplifies performance troubleshooting by helping you quickly find performance differences caused by query plan changes.

Why I have to use Query Store: Quey store automatically capture a history of queries, plan, and runtime statistics, and retain these for your review. well if you want to choose the hard path to solve performance issues then don't use query store.

How to use Query Store: Well I like this question, I will try to explain whatever I understood.

1. Go to SQL Server Mangement Studio
2. Object Explorer, right-click on a database, and select properties
3. From Database Properties window select Query Store Page
4.  From Operation Mode( Requested) Select read write or read only

    *** you cannot enable Query Store for master and tempdb
After enabling Query store, go to required Database --> Query Store

Query Store will log information about each query including:
1. Number of executions
2. execution time
3. Memory consumption
4. Logical Reads
5. Logical Writes
6. Physical Reads
7. Number of execution plan changes

To reduce the load on the server, this information is aggregated into a fixed window. If you need more precise data, you should look to Extended Events.

Now open regressed queries view. you will see a similer window like below.

This tool will allow you to see regressions based on any of the recorded metrics. If you see a regression, you have the option to force SQL Server to use an older execution plan.

How to add additional user to Azure Subscription

1. 1. Login to portal.azure.com 
2. 2. Search by subscription 
3. 3. Select right subscription name 
4. 4. Select Access Control(IAM) 
5. 
   5. Click on Add
6. 
   
7. 6. Select role you want to provide and provide Microsoft email  and click on save
8. 
   
9. 
   
   
10. 7. That all done. Now new user can access portal with provided permissions

How to Enable or Disable Index on Table using SQL

Index Status: 

select 

    sys.objects.name, 

    sys.indexes.name 

from sys.indexes 

    inner join sys.objects on sys.objects.object_id = sys.indexes.object_id 

where sys.indexes.is_disabled = 1 

order by 

    sys.objects.name, 

    Sys.indexes.name 

----------------------------------------------------------------------------------- 

Index Disable: 

use DB 

go 

ALTER INDEX Index_name ON Table_name 

DISABLE; 

-------------------------------------------------------------------------------- 

Index Enable: 

use ACTIVEQUOTE 

go 

ALTER INDEX Index_name ON Table_name 

rebuild

Find most used tables & Most used Indexes & Unused Index & Missing Index Script

--get most used tables

SELECT  

    db_name(ius.database_id) AS DatabaseName, 

    t.NAME AS TableName, 

    SUM(ius.user_seeks + ius.user_scans + ius.user_lookups) AS NbrTimesAccessed 

FROM sys.dm_db_index_usage_stats ius 

INNER JOIN sys.tables t ON t.OBJECT_ID = ius.object_id 

WHERE database_id = DB_ID('ACTIVEQUOTE') 

GROUP BY database_id, t.name 

ORDER BY SUM(ius.user_seeks + ius.user_scans + ius.user_lookups) DESC

--get most used indexes

SELECT  db_name(ius.database_id) AS DatabaseName, t.NAME AS TableName, i.NAME AS IndexName, i.type_desc AS IndexType, ius.user_seeks + ius.user_scans + ius.user_lookups AS NbrTimesAccessed FROM sys.dm_db_index_usage_stats ius INNER JOIN sys.indexes i ON i.OBJECT_ID = ius.OBJECT_ID AND i.index_id = ius.index_id INNER JOIN sys.tables t ON t.OBJECT_ID = i.object_id WHERE database_id = DB_ID('MyDb') ORDER BY ius.user_seeks + ius.user_scans + ius.user_lookups DESC

-- Get top 25 Unused Indexes

SELECT TOP 25 

o.name AS ObjectName 

, i.name AS IndexName 

, i.index_id AS IndexID 

, dm_ius.user_seeks AS UserSeek 

, dm_ius.user_scans AS UserScans 

, dm_ius.user_lookups AS UserLookups 

, dm_ius.user_updates AS UserUpdates 

, p.TableRows 

, 'DROP INDEX ' + QUOTENAME(i.name) 

+ ' ON ' + QUOTENAME(s.name) + '.' 

+ QUOTENAME(OBJECT_NAME(dm_ius.OBJECT_ID)) AS 'drop statement' 

FROM sys.dm_db_index_usage_stats dm_ius 

INNER JOIN sys.indexes i ON i.index_id = dm_ius.index_id  

AND dm_ius.OBJECT_ID = i.OBJECT_ID 

INNER JOIN sys.objects o ON dm_ius.OBJECT_ID = o.OBJECT_ID 

INNER JOIN sys.schemas s ON o.schema_id = s.schema_id 

INNER JOIN (SELECT SUM(p.rows) TableRows, p.index_id, p.OBJECT_ID 

FROM sys.partitions p GROUP BY p.index_id, p.OBJECT_ID) p 

ON p.index_id = dm_ius.index_id AND dm_ius.OBJECT_ID = p.OBJECT_ID 

WHERE OBJECTPROPERTY(dm_ius.OBJECT_ID,'IsUserTable') = 1 

AND dm_ius.database_id = DB_ID() 

AND i.type_desc = 'nonclustered' 

AND i.is_primary_key = 0 

AND i.is_unique_constraint = 0 

ORDER BY (dm_ius.user_seeks + dm_ius.user_scans + dm_ius.user_lookups) ASC 

GO

-- Get top 25 missing Index

SELECT TOP 25 

dm_mid.database_id AS DatabaseID, 

dm_migs.avg_user_impact*(dm_migs.user_seeks+dm_migs.user_scans) Avg_Estimated_Impact, 

dm_migs.last_user_seek AS Last_User_Seek, 

OBJECT_NAME(dm_mid.OBJECT_ID,dm_mid.database_id) AS [TableName], 

'CREATE INDEX [IX_' + OBJECT_NAME(dm_mid.OBJECT_ID,dm_mid.database_id) + '_' 

+ REPLACE(REPLACE(REPLACE(ISNULL(dm_mid.equality_columns,''),', ','_'),'[',''),']','')  

+ CASE 

WHEN dm_mid.equality_columns IS NOT NULL 

AND dm_mid.inequality_columns IS NOT NULL THEN '_' 

ELSE '' 

END 

+ REPLACE(REPLACE(REPLACE(ISNULL(dm_mid.inequality_columns,''),', ','_'),'[',''),']','') 

+ ']' 

+ ' ON ' + dm_mid.statement 

+ ' (' + ISNULL (dm_mid.equality_columns,'') 

+ CASE WHEN dm_mid.equality_columns IS NOT NULL AND dm_mid.inequality_columns  

IS NOT NULL THEN ',' ELSE 

'' END 

+ ISNULL (dm_mid.inequality_columns, '') 

+ ')' 

+ ISNULL (' INCLUDE (' + dm_mid.included_columns + ')', '') AS Create_Statement 

FROM sys.dm_db_missing_index_groups dm_mig 

INNER JOIN sys.dm_db_missing_index_group_stats dm_migs 

ON dm_migs.group_handle = dm_mig.index_group_handle 

INNER JOIN sys.dm_db_missing_index_details dm_mid 

ON dm_mig.index_handle = dm_mid.index_handle 

WHERE dm_mid.database_ID = DB_ID() 

ORDER BY Avg_Estimated_Impact DESC 

GO