Find out ENABLED GUEST user in all registered SQL Servers with HTML output via PowerShell

Use PowerShell to determine which registered SQL Servers have the ENABLED GUEST user with HTML output.

Tasks:

1. Extract the list of all registered SQL servers.
2. Get a T-SQL script ready.
3. Get the HTML and CSS style ready.
4. Combine them in PowerShell.

In most cases, locating a user within a SQL server is rather simple. The new system table or the conventional system table can be used.

sysusers
sys.database_principals

However, the user's enabled or disabled status, which is only available through the table, is: sys.sysusers. Let's get right to the coding.

[SCSM] Microsoft System Center SQL Server Permissions

After a two-week long time of studying the document and having a lots conversations with the vendor and System admin. I finally figured out the security scope for the SQL DBs on SCSM.

Document download link: http://www.microsoft.com/en-us/download/details.aspx?id=27850

Neither the vendor or the document would cover 100% correct settings on practical environment. At least not mine in my company.

I will list the summary below

[Permission] GRANTED "CREATE" still get "CREATE" WARNING through SSMS GUI

"You are not logged in as the database owner or as a user that is a member of the db_owner role"
Solution:
obviously, you need to grant the account as DB_OWNER. That's only for avoid the annoying GUI warning. Otherwise, "CREATE TABLE" is enough for Creating Tables. 

Severity: 14, Logon Login failed . Reason: Failed to open the explicitly specified Database

I tried to figure it out from someone recommend to find out who tried to login the database.
The user is me and is a database sysadmin. So it slap backup the wrong solution.
What caused it?

SQL Server Error: 18456/18452 SQLState:28000

Connection Failed;
sqlstate '28000';
sql server error: 18456;
[Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user 'XXXX'

Get Last Tables Access or Structure Changed on SQL Server

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;

[T-SQL] Recursive T-SQL to do “DENY IMPERSONATE ON LOGIN”

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.

SQL Login User Dropped Failed

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.

[SQL]Get SQL Connections Sessions TCP Statement Info - Part 1

Purposes: Collection of Connections, Sessions, T-SQLs..etc

• These DMV could do a collection of I/O-related database management objects(DMOs), also will help you investigate the related I/O info 
• I/O investigation derived from 

DMVs Used:

    --sys.dm_exec_connections, http://msdn.microsoft.com/en-us/library/ms181509.aspx
    --sys.dm_exec_sessions, http://msdn.microsoft.com/en-us/library/ms176013.aspx
    --sys.dm_exec_requests, http://msdn.microsoft.com/en-us/library/ms177648.aspx
  
    --sys.dm_exec_query_stats, http://msdn.microsoft.com/en-us/library/ms189741.aspx
    --sys.dm_exec_sql_text, http://msdn.microsoft.com/en-us/library/ms181929.aspx