SQL Server : Check Linked servers pointing to Primary or Secondary

-- Script to check Linked servers pointing to Primary or Secondary

Declare @lsrv_name sysname

declare @exctbl varchar(500);

Select @lsrv_name = 'AGN6_SQ1_PL_Test_RO'  -- change linked server name here

If exists (Select 1 from tempdb..sysobjects where xtype = 'u' and name like '#data%')

Begin

Drop table #data 

End 

Create table #data(Replica_type varchar(100), Linked_Srvr Sysname)

If exists (select * from sys.sysservers where srvname like @lsrv_name)

Begin

set @exctbl = 'SELECT ars.role_desc, '''+ @lsrv_name +''' as Linked_srv_name

FROM ' + @lsrv_name +'.master.sys.dm_hadr_availability_replica_states ars 

INNER JOIN ' + @lsrv_name +'.master.sys.availability_groups ag 

ON ars.group_id = ag.group_id

AND ars.is_local = 1'

print (@exctbl);

insert into  #data  exec(@exctbl)

END

select * from #data

Drop table #data

SQL Server : Guest user status on all databases

Below script will only list the DB's where guest user is enabled.

create table #guest_users

(

ServerName nvarchar(50),

DBName nvarchar(200),

name sysname,

Status nvarchar(50)

)

EXEC sp_MSforeachdb

'USE [?];

Insert into #guest_users

SELECT    @@Servername AS ServerName,

DB_NAME() AS DBName,

name,

case hasdbaccess when 1 then ''Enabled'' else ''Disabled'' end AS Status

FROM sysusers where name like ''guest'' AND hasdbaccess = 1;'

Select * from #guest_users

drop table #guest_users

--- Below script will list the DB's where guest user is enabled/disabled with value 

CREATE TABLE #guest_users

(

       ServerName nvarchar(50),

       DBName nvarchar(200),

       name sysname,

       Status nvarchar(50)

)

EXEC sp_MSforeachdb

'USE [?];

Insert into #guest_users

SELECT @@Servername AS ServerName,

             DB_NAME() AS DBName,

             name,

             case hasdbaccess when 1 then ''Enabled'' else ''Disabled'' end AS Status

       FROM sysusers where name like ''guest'';'

SELECT * FROM #guest_users

DROP TABLE #guest_users

SQL Server : Truncate all Tables of a Database

A script to truncate all tables of a Database in Sql Server.

One of major the problem with table truncation is, we need to remove if there are any foreign key’s defined in other tables which references the columns in the table to be truncated. So to truncate a table we need to first remove all the foreign key references then truncate the table and finally add back the removed foreign key constraints.

SET NOCOUNT ON
GO
--Get the list of all the tables to be truncated
 DECLARE @TablesToBeTruncated AS TABLE
 (Id INT IDENTITY(1,1),TableObjectId INT, TableName SYSNAME,
    SchemaId INT)
INSERT INTO @TablesToBeTruncated
 SELECT ST.object_id,ST.name,ST.schema_id
 FROM sys.Tables ST
 WHERE ST.type = 'U' AND ST.NAME NOT LIKE '#%'
 AND ST.name <> 'sysdiagrams'
 --AND ST.NAME NOT IN ('') -- Specify here the comma separated table names for which truncation is not required
 --AND ST.NAME IN ('') -- Specify here the comma separated table names which needs to be truncated

 --Generate the foreignkeys drop and create back script
DECLARE @CreateScript AS NVARCHAR(MAX), @DropScript AS NVARCHAR(MAX)
SELECT
    ------------DROP SCRIPT--------------------
    @DropScript = ISNULL(@DropScript,'') + 'ALTER TABLE ' + QUOTENAME(SCHEMA_NAME(Tlist.SchemaId)) + '.'
     + QUOTENAME(OBJECT_NAME(FKey.parent_object_id)) + ' DROP CONSTRAINT ' + QUOTENAME(FKey.name)
     + CHAR(10),
     -----------CREATE BACK SCRIPT-------------
    @CreateScript = ISNULL(@CreateScript,'') + 'ALTER TABLE ' + QUOTENAME(SCHEMA_NAME(Tlist.SchemaId)) + '.'
     + QUOTENAME(OBJECT_NAME(FKey.parent_object_id)) + ' ADD CONSTRAINT ' + QUOTENAME(FKey.name)
     + ' FOREIGN KEY ' + '(' + STUFF(( -- Get the list of columns
                 SELECT ',' + QUOTENAME(COL_NAME(FKeyCol.parent_object_id, FKeyCol.parent_column_id))
                 FROM SYS.FOREIGN_KEY_COLUMNS FKeyCol
                 WHERE FKey.OBJECT_ID = FKeyCol.constraint_object_id
                 ORDER BY FKeyCol.constraint_column_id
                 FOR XML PATH('')),1,1,'') + ')'
     + ' REFERENCES ' + QUOTENAME(SCHEMA_NAME(Tlist.SchemaId)) + '.'
                + QUOTENAME(OBJECT_NAME(FKey.referenced_object_id)) + ' (' + STUFF(( -- Get the list of columns
                SELECT ',' + QUOTENAME(COL_NAME(FKeyCol.referenced_object_id, FKeyCol.referenced_column_id))
                FROM SYS.FOREIGN_KEY_COLUMNS FKeyCol
                WHERE FKey.OBJECT_ID = FKeyCol.constraint_object_id
                ORDER BY FKeyCol.constraint_column_id
                FOR XML PATH('')),1,1,'') + ') '
     + CASE WHEN update_referential_action_desc = 'CASCADE' THEN ' ON UPDATE CASCADE'
            WHEN update_referential_action_desc = 'SET_DEFAULT' THEN ' ON UPDATE SET DEFAULT'
            WHEN update_referential_action_desc = 'SET_NULL' THEN ' ON UPDATE SET NULL'
            ELSE ''
       END
     + CASE WHEN delete_referential_action_desc = 'CASCADE' THEN ' ON DELETE CASCADE'
            WHEN delete_referential_action_desc = 'SET_DEFAULT' THEN ' ON DELETE SET DEFAULT'
            WHEN delete_referential_action_desc = 'SET_NULL' THEN ' ON DELETE SET NULL'
            ELSE ''
       END  + CHAR(10)
 FROM @TablesToBeTruncated Tlist
            INNER JOIN SYS.FOREIGN_KEYS FKey
                ON Tlist.TableObjectId = FKey.referenced_object_id

--PRINT THE TRUNCATION SCRIPT
IF LEN(ISNULL(@DropScript,'')) > 0
 BEGIN
     PRINT CHAR(10) + ' GO ' + CHAR(10) + '--------DROP FOREIGN KEY CONSTRAINTS SCRIPT--------'
     PRINT @DropScript + CHAR(10) + ' GO ' + CHAR(10)
 END

PRINT '--------TRUNCATE TABLES SCRIPT--------'
--TRUNCATE TABLES
DECLARE @id INT,@truncatescript NVARCHAR(MAX)
SELECT @id = MIN(Id)FROM @TablesToBeTruncated
WHILE @id is not null
 BEGIN
     SELECT @truncatescript = 'TRUNCATE TABLE ' + QUOTENAME(SCHEMA_NAME(SchemaId)) + '.' + QUOTENAME(TableName)
     FROM @TablesToBeTruncated WHERE Id = @id
     PRINT @truncatescript
     SELECT @id = MIN(Id)FROM @TablesToBeTruncated WHERE Id > @id
 END

IF LEN(ISNULL(@CreateScript,'')) > 0
 BEGIN
     PRINT CHAR(10) + ' GO ' + CHAR(10) + '--------CREATE BACK THE FOREIGN KEY CONSTRAINTS SCRIPT--------'
     PRINT CAST((@CreateScript + CHAR(10) + ' GO ' + CHAR(10)) AS NTEXT)
 END
 GO

SQL Server : Stored Procedures Ignore User’s Permissions

According to this MSDN documentation :

Stored procedures take advantage of ownership chaining to provide access to data so that users do not need to have explicit permission to access database objects. An ownership chain exists when objects that access each other sequentially are owned by the same user. For example, a stored procedure can call other stored procedures, or a stored procedure can access multiple tables. If all objects in the chain of execution have the same owner, then SQL Server only checks the EXECUTE permission for the caller, not the caller’s permissions on other objects. Therefore you need to grant only EXECUTE permissions on stored procedures; you can revoke or deny all permissions on the underlying tables.

I've came across a situation where proc was having insert statement and user had just read access however user was still able to run proc. Obviously, it has execute on proc but no insert on underlying table. 

The Bottom Line!

If you are working with sensitive data, be aware that your users may still be able to access that data via stored procedures, even if you have explicitly given DENY permission on your sensitive tables.

SQL Server : List all permissions for a given role

Following script will give ALL permissions for an associated role.

SELECT DB_NAME() AS 'DBName'
      ,p.[name] AS 'PrincipalName'
      ,p.[type_desc] AS 'PrincipalType'
      ,p2.[name] AS 'GrantedBy'
      ,dbp.[permission_name]
      ,dbp.[state_desc]
      ,so.[Name] AS 'ObjectName'
      ,so.[type_desc] AS 'ObjectType'
  FROM [sys].[database_permissions] dbp LEFT JOIN [sys].[objects] so
    ON dbp.[major_id] = so.[object_id] LEFT JOIN [sys].[database_principals] p
    ON dbp.[grantee_principal_id] = p.[principal_id] LEFT JOIN [sys].[database_principals] p2
    ON dbp.[grantor_principal_id] = p2.[principal_id]

WHERE p.[name] = 'users'

SQL Server : changes from primary replica are not reflected on secondary replica for an Always On availability group

The client application completes an update on the primary replica successfully, but querying the secondary replica shows that the change is not reflected. This case assumes that your availability has a healthy synchronization state. In most cases, this behavior resolves itself after a few minutes.
If changes are still not reflected on the secondary replica after a few minutes, there may be a bottleneck in the synchronization work flow. The location of the bottleneck depends on whether the secondary replica is set to synchronous commit or asynchronous commit.

Following article has detail around it but i just thought to jot down exact point which helped me to resolve this issue. 

https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/troubleshoot-primary-changes-not-reflected-on-secondary?view=sql-server-2017

As stated above, AlwaysOn dashboard was showing , secondary replica in synchronized state so ideally, both copies should have exact same data but that was not the case. 

Uopn further checking, I observed that that was heavy blocking to I've extracted root of blocking chain and killed those post approval obviously.  

As soon as blocking got clear, data got in sync.

Diagnosis and resolution

You can use the following DMV query to see how far the redo thread has fallen behind, by measuring the difference between the gap between last_redone_lsn and last_received_lsn.

select recovery_lsn, truncation_lsn, last_hardened_lsn, last_received_lsn,   
   last_redone_lsn, last_redone_time  
from sys.dm_hadr_database_replica_states  

Brgds,



Chhavinath Mishra

Sr. Database Administrator





Microsoft Certified IT Professional (MCITP)