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.

Situation:

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.

[SNMP TRAP: 15006 in CPQCLUS.MIB]

No comments

2014-10-10

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...
OMG..