Get SQL Service Accounts

1 comment


Recently our Domain team created an exclusive OU for DBA. I’d like to control all the SQL service accounts. But SQL server not only has SQL Engine and SQL Agent accounts but also SSIS/SSAS/SSRS/Full Text, etc.....
How do I know all the accounts in SQL Server? If you have SQL 2008+, you can use a new DMV “sys.dm_server_services”.
If you have SQL 2005, you have to use undocumented “xp_regread” function to read registry records that in Windows Server.
It’s also fit to SQL 2008+…

Automated Database Restore Script Out

No comments



In order to be compatible with SQL 2005 ( no default value for T-SQL variable), I changed the code script method.

A lot of posts that used “RESTORE FILELISTONLY” and “BackupSet” table to grab backup files.

Yes, I agree it would be more accurate for latest backup and correct LSN/DB logical name. But if someone drop/detach DB and left backup histories and backup files. It would be incorrect. Additional, sometimes when you use “RESTORE FILELISTONLY” to grab backup files, it will show “Access Denied” message.

In order to avoid the problem, I use “sys.Master_files” table to match “Backupset” and “BackupsetMediafamily”.

Any Array value if you have like


Always, Always put "| Out-Null" behind to prevent the null element feed Function first and get "False" then return "0" back.
So your Table doesn't need to add one more "Ghost" column

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?

I created the customized SQL Endpoint for 3rd party vendor uses. I grant "Connection" permission for the account. Deny "Public" connect to Default [Tcp] Endpoint.
Turn on Firewall (ex: port 4999), Allow Remote Connection in SQL, Enable Tcp portocol, Name Pipe, SharedMemory. But it still showed the Error 64 even if the account was successfully established.

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

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...
1. Make sure your "Theme" service is running
2. Then "System Properties" -> "Advanced" -> "Performance"
3. Make Sure "Use Visual Styles on windows and buttons" is checked.
If failed, repeat to 1-3 or restart. It should be fixed

Here is the error:
Msg 214, Level 16, State 201, Procedure xp_cmdshell, Line 1
Procedure expects parameter 'command_string' of type 'varchar'.
Because xp_cmdshell CANNOT use varchar(max) with xp_cmdshell
I dislike when I click the icons on the Windows taskbar then the icon will become large.
It's fine when you just need click one icon at a time.
But if you want to click two and more icons quickly at a short time?
Sometimes you will click the software twice or more especially Outlook

It's one of solutions...

--Check DB state
select state_desc,name,user_access_desc from sys.databases where name='xxxxx'

refer to
Errors like this:

Msg 1807, Level 16, State 3, Line 1
Could not obtain exclusive lock on database 'model'. Retry the operation later.
Msg 1802, Level 16, State 4, Line 1
CREATE DATABASE failed. Some file names listed could not be created. Check related errors.

I can search through 2 charactors that suck like "SE", or "XO"
But still cannot search for a word like "Security". How to fix it?
In the Google Drive Folder, select
1) Organize -> Folder and Search Options
2) Search Tab -> Check "'Don't use the index when searching in file folders for system files"

SQL SSMS CTRL+R Not working

No comments


Tools-> Environment -> Keyboard ->Reset
Suspending and restarting VMware itself.
Restart the service VMware Workstation Server on host
taskkill /F /IM vmtoolsd.exe
"C:\Program Files\VMware\VMware Tools\vmtoolsd.exe" -n vmusr
Trust, reinstall VMTools is not working.

If you already installed Oracle Client software and have an Oracle Login ready to be used as a Replication user. Below is the solution

Replication has 3 roles: Publication, Distribution and Subscription.
The source is from Oracle, and I want the data from Oracle push to SQL Server and not updatable.
Publication: Oracle
Distribution: SQL Server A
Subscriptions: SQL Server A ( I installedont the same Server/Instance), you can add/change it if you want.

[SQL]Index Usage Practice

No comments


Today’s practice is “Index usage”.  
Database: Adventurework2012.   Table: Sales.Customer.     T-SQL Scripts: 8 segments
Why i chose the table? Because the table is simple and has 4 Indexes. No including filter, XML index. So it’s simple to test.

To help students stay current with upcoming release of SQL Server 2014, all MCSE-level SQL exams (464 through 467) will be updated on April 24, 2014 to cover solutions based on both SQL Server 2012 and 2014 products.

Recently, I’ve found one of my server has lots lock: timeout happened. I am curious what caused this? So I opened SQL Server Profiler to watch the server


The online index rebuild can cause index corruption or data loss when it is used together with concurrent queries that modify many rows in Microsoft SQL Server 2012 Service Pack 1 or Microsoft SQL Server 2014. 

This issue occurs when the following conditions are true:
  • The online index build query runs in parallel mode.
  • Deadlock error and a fatal error such as "lock timeout" occur in a specific order

FIX: KB2969896

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:


[SQL]Remove TempDB and meet an issue

No comments


One of the DB I/O issues encountered, and I found the "Log" file and the "Data" file located at the same folder/ the same LUN.

So I need to move the "Log" file to improve the performance.

Environment: Clustered

Preliminary: move the instance to the other node.

Command: Type the command to move tempdb Log file

(NAME = templog, FILENAME = 'H:\MSSQL\Data\templog.ldf')

Then if I want to DELETE the old tempdb.ldf, I have to restart SQL Server service

But I forgot, it's on Clustered environment, so when I opend SSMS, I got an error below

Property Size is not available for Database '[tempdb]'

[SQL]DeadLock practice

No comments


3 Sessions, 2 Tables, 2 Transactions for Updating, 3 "SELECT" statements

SessionTable NamesT-SQL
52Sale.Category  and Sale.CarBEGIN TRAN, SELECT
53Sale.Car and Sale.CategoryBEGIN TRAN, SELECT