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%'

How to download SQL Server 2016 / 2017 Free

How to download SQL Server 2016 / 2017 Free:

It's simple please go through below link to download free, nowadays Microsoft giving Developer edition free download, because they love developers and you get all feature whatever you get in the enterprise version, soo cool right, but only one catch you can't use developer for your production environment.

Please find the difference in below table.

https://www.microsoft.com/en-us/sql-server/sql-server-downloads

Features	SQL Server 2016 Enterprise	SQL Server 2016 Standard	SQL Server 2016 Express	SQL Server 2016 Developer
Maximum number of cores	Unlimited	24 Cores	4 Cores	Unlimited
Maximum memory utilized per instance	Operating system max	128 GB	1 GB	Operating system max
Maximum size	524 PB	524 PB	10 GB	524 PB
Production use rights	Yes	Yes	Yes	No
Basic OLTP	Yes	Yes	Yes	Yes
Manageability: Management Studio, policy-based management	Yes	Yes	Yes	Yes
Basic high availability: 2-node single database failover, non-readable secondary	Yes	Yes	NO	Yes
Enterprise data management: Master Data Services, Data Quality Services	Yes	NO	NO	Yes
Advanced OLTP: In-memory OLTP, operational analytics	Yes	NO	NO	Yes
Advanced High Availability: Always On Availability Groups, multi-database failover, readable secondaries	Yes	NO	NO	Yes
Basic security: Row-level security, data masking, basic auditing, separation of duties	Yes	Yes	NO	Yes
Advanced security: Transparent database encryption, Always Encrypted	Yes	No	NO	Yes
Advanced data integration: Fuzzy grouping and lookups, change data capture	Yes	NO	NO	Yes
Data warehousing: In-Memory Columnstore, partitioning	Yes	No	NO	Yes
PolyBase2	Yes	Yes	NO	Yes
Maximum memory utilized per instance of Analysis Services	Operating system max	Tabular: 16 GB MOLAP: 64 GB	No	NO
Maximum memory utilized per instance of Reporting Services	Operating system max	64 GB	Express with Advanced Services: 4 GB	NO
Programmability and developer tools: T-SQL, CLR, Data Types, FileTable, JSON	Yes	Yes	Yes	Yes
Basic reporting and analytics	Yes	Yes	No	Yes
Basic data integration: SQL Server Integration Services, built-in connectors	Yes	Yes	No	Yes
Basic corporate business intelligence: Basic multi-dimensional models, basic tabular model, in-memory storage mode	Yes	Yes	No	Yes
Mobile reports and KPIs	Yes	No	NO	Yes
Advanced corporate business intelligence: Advanced multi-dimensional models, advanced tabular model, DirectQuery storage mode, advanced data mining	Yes	NO	NO	Yes
Basic R integration: Connectivity to R open, limited parallelism	Yes	Yes	Yes	Yes
Advanced R integration: Full parallelism ScaleR	Yes	NO	NO	Yes
Hybrid cloud Stretch Database	Yes	Yes	Yes	Yes

If you have any questions, please comment below, I will try to reply back ASAP

SSMS unable to read/find .bak files or .mdf or .ldf files

Problem:

I have a problem this morning, SQL Server.Bak,.ldf, .mdf files available when I see physically, but SSMS can't read this files from the navigation bar.


Solution:

"The reason it won't "open" the folder is that the service account running the SQL Server Engine service does not have read permission on the folder in question. Assign the windows user group for that SQL Server instance the rights to read and list contents at the WINDOWS level. Then you should see the files that you want to attach inside of the folder."

or else

Change SQL Server Service account to Local windows admin account, your problem will be solved.

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

Can Instance Level Collation be different from Database Collation?

Yes

Difference between instance level vs database level

Instance level collation represent server level collation, but database collation represent  that particular database.

Go to SQL Server instance àright click go to properties àGeneral àyou can see server level collation

Now select database got to àproperties àOptions àyou can see collation

Even if you change database collation it’s won’t be any affect on server level collation.

Is it best practice to have auto shrink enable on database?

You can say straight forward NO

Why?

If you have auto shrink enabled in your environment then it increases the fragmentation level of indexes so performance will go down

Even you save some space after auto shrink but it’s not recommendable.

A DBA should have control on shrink, but with auto shrink you won’t be having control on database.

If you have a table with 50 gig and it’s got historical data and you don’t need any more in that database then you got go for truncate whole data in a table then it would be a good practice to shrink data file on that database, if log became huge and you don’t have any other option then you can shrink log but it is preferable to shrink manually 

What is IO affinity?

We 2 type of affinity CPU affinity and IO affinity these two affinities’s collectively called as processor affinity.

If you have multiple CPU running in single machine and reading data from disks. If IO operation performance is good then SQL Server performance also will be good. We can assign resource to CPU affinity and IO affinity.

Go SQL Server Instance right click à go to Properties à Processor you will see below options

1      Automatically set processor affinity mask for all processors

2     Automatically set I/O affinity mask for all processors

By selecting these options you can see all CPU available. So now you can select which CPU’s which you want  use for I/O affinity.

What is locking in SQL Server?

Locking is default behavior of SQL Server it’s not just SQL server it also applicable for any DB, this is important because to maintain accurate data it happens like explained below.

Type of locks:

SQL server puts row lock on row, key, page, extent, table and DB

Modes of locks:

Shared (S): if a transaction reading from a table and allows other transaction can come in and read the data

Update (U): if update is happening no other transaction can come in and give data to application.

Exclusive (X):  updated lock can be converted as exclusive lock, but if exclusive lock happens no other lock can be taken

Intent:  if update or shared lock is happening and other transaction waiting in queue then it’s called an intent lock

Schema: if there are any changes happening like delete, update in table then schema lock will happen

Bulk Update (BU):if bulk update happen then bulk update lock will happens

All of the above behaves differently according to isolation level