SELECT DB_NAME(ius.[database_id]) AS [Database],
OBJECT_NAME(ius.[object_id]) AS [TableName],
MAX(ius.[last_user_lookup]) AS [last_user_lookup],
MAX(ius.[last_user_scan]) AS [last_user_scan],
MAX(ius.[last_user_seek]) AS [last_user_seek]
FROM sys.dm_db_index_usage_stats AS ius
WHERE ius.[database_id] = DB_ID()
AND ius.[object_id] = OBJECT_ID('<tableName>')
GROUP BY ius.[database_id], ius.[object_id];
--For all tables in a DB
DECLARE @tmpTableAccess TABLE(
sDatabase NVARCHAR(255),
sTable NVARCHAR(255),
dDateLastLookup DATETIME,
dDateLastScan DATETIME,
dDateLastSeek DATETIME)
DECLARE @TableName varchar(255)
DECLARE TableCursor CURSOR FOR
SELECT table_name FROM INFORMATION_SCHEMA.TABLES
WHERE table_type = 'base table'
AND table_schema='dbo'
OPEN TableCursor
FETCH NEXT FROM TableCursor INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
--PRINT 'Processing: ' + @TableName
INSERT INTO @tmpTableAccess
SELECT DB_NAME(ius.[database_id]) AS [Database],
OBJECT_NAME(ius.[object_id]) AS [TableName],
MAX(ius.[last_user_lookup]) AS [last_user_lookup],
MAX(ius.[last_user_scan]) AS [last_user_scan],
MAX(ius.[last_user_seek]) AS [last_user_seek]
FROM sys.dm_db_index_usage_stats AS ius
WHERE ius.[database_id] = DB_ID()
AND ius.[object_id] = OBJECT_ID(@TableName)
GROUP BY ius.[database_id], ius.[object_id];
FETCH NEXT FROM TableCursor INTO @TableName
END
CLOSE TableCursor
DEALLOCATE TableCursor
SELECT * FROM @tmpTableAccess
--DROP TABLE @tmpTableAccess
USE MASTER
GO
DENY VIEW ANY DATABASE TO [domain\Group Name]
GO
--https://www.mssqltips.com/sqlservertip/2995/how-to-hide-sql-server-user-databases-in-sql-server-management-studio/
SELECT
who.name AS [Principal Name] from sys.server_principals who
where who.name NOT IN (
SELECT
who.name AS [Principal Name]
FROM
sys.server_permissions what
INNER JOIN sys.server_principals who
ON who.principal_id = what.grantee_principal_id
WHERE
what.permission_name = 'View Any Database'
AND who.name NOT LIKE '##MS%##'
AND who.type_desc <> 'SERVER_ROLE'
----------------Changed to GRANT
--AND what.state_desc='GRANT'
)
order by who.name