There are two facts of table that DBA want to check

1. For Tables structural change

IF OBJECT_ID('tempdb..#lastaccess1') IS NOT NULL
DROP TABLE #lastaccess1;
CREATE TABLE #lastaccess1(
    [DBName] sysname,
    [TableNames] NVARCHAR(256),
    [create_date] datetime,
    [modify_date] datetime,

DECLARE @Statement1 VARCHAR(4000)
SET @Statement1 = 'USE [?]
SELECT  ''?'' as [DBNAME],  [TableName] = name,
create_date, modify_date
FROM    sys.tables
ORDER BY modify_date DESC'
INSERT INTO #lastaccess1([DBName] ,[TableNames] ,[create_date], [modify_date] )
EXEC sp_MSforeachdb @Statement1;
SELECT * FROM #lastaccess1 WHERE [DBNAME] not in ('master', 'msdb','model','tempdb');
DROP TABLE #lastaccess1;

The situation depends on what your SQL environment are? For my case, I need to “DENY IMPERSONATE ON LOGIN::” to most of DBA logins and general Logins except real necessary DBAs. But I don’t like to COPY/PASTE command for lots users (if your server has over 20 accounts), so I created a script to do this.

It’s an inefficient T-SQL script due to my desperate boss wants to see the results for all SQL instances… if you can modify it for me, I will be very appreciated.


The User1 is no longer in the company and only "Public" server role. When I type command:
"Drop Login [xxx\User1]", it showed the errors below
Login 'xxx\User1' has granted one or more permission(s). Revoke the permission(s) before dropping the login.


No comments


Today I've tried failover the server to the other one. It's been failed all the time but not the disks or network adapter.
Just SQL engine and SQL agent..... I cannot find any answers online.......
So I was disappointed and want to reinstall it. When I did that , I found one thing was very interesting..
The DB that our junior DBA installed was not installed as the Failover node but a stand-alone...