Get SQL Service Accounts

No comments

2014-12-12

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

2014-12-08

Limitation:

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