Last access on table

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

 

 

Grants and Denys for a Windows account on a table

GRANT SELECT, INSERT, UPDATE, DELETE ON dbo.Tablename TO [domain\user]; 
 

Deny View on all databases to a specific user/group

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/


Query users that *Can* View All Databases


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