Tuesday, 8 May 2012

SQLCMD helps DBAs at the time of crisis

We do set default DB for logins other than master database in SQL server for security reasons. There is a scenario if default database gets offline or corrupts. 

Suppose default database is XYZ that goes offline or corrupts then SQL does not allow connection to all logins those having XYZ as a default database. Because it tries to connect the default database first. 

Thanks to SQLCMD that has a way to get in, using to -d switch

sqlcmd -S<servername> -d <Other_database_Name_ABC> -U<login> -P<password>

It  is helpful in case we disable Built-In logins, SA and don't have choice of other logins to connect to SQL server.

Sunday, 30 October 2011

SQL Login locked out status check script

This script is designed to find the logins those are locked out in the database Server.
It is tested to run on:
SQL 2000 - NO (Login lock feature was not introduced in this SQL version)
SQL 2005 - YES
SQL 2008 - YES
set nocount on
declare  Cur_locked_login cursor for select name from master..syslogins order by name
declare @loginname nvarchar(100)
open Cur_locked_login
fetch next from Cur_locked_login into @loginname
while @@FETCH_STATUS >= 0
if (select LOGINPROPERTY(@loginname,'islocked')) =1
print 'Login -> ' + @loginname +' is LOCKED'
fetch next from Cur_locked_login into @loginname
close Cur_locked_login
deallocate Cur_locked_login

Thursday, 27 October 2011

Run SQLCMD from SQL 2008 management studio query window

Microsoft embedded sqlcmd to run thru query-window. Now you can run SQLCMD without enabling xp_cmdshell from SQL management Studio. ( As you know, it is risky to enable this surface area configuration parameter ).

Open Management Studio -> Query -> SQLCMD Mode
Now, you can run a sqlcmd statements from a query window like below -
!!sqlcmd -S<ServerName> -E -Q"select * from sysfiles"

If you don't have a tool to run query against multiple database servers. No worries !! You can add your all database servers as a linked server in a server and run SQLCMD to query against multiple servers.

If you have more than 50 servers, there are many SQL diagnosis tools are available in the market. I worked with idera multi query tool. It is easy to use. There are a lot other things can be done using this tool. You can get more details here -

Database backup tool

SQL database BackupTool:
hyperbac is a tool to run the compressed SQL backups. It has feature to encrypt the backupfile. Why the encryption is required for backup !! In many organization there is policy to FTP the backup file and most of the customers now a days has mandate to provide encrypted backup file. In your organization you can refer this tool.

More important, hyperbac can be used to restore a single table from a backup file. You just need to have to create a LINKEDSERVER and you can fire query against object.

You can check more details -

Popular Posts

Search This Blog