Generate create user script for all server logins

sp_help_revlogin Store procedure generates create user script for all the server logins.

USE master
GO
IF OBJECT_ID ('sp_hexadecimal') IS NOT NULL
DROP PROCEDURE sp_hexadecimal
GO
CREATE PROCEDURE sp_hexadecimal
@binvalue varbinary(256),
@hexvalue varchar (514) OUTPUT
AS
DECLARE @charvalue varchar (514)
DECLARE @i int
DECLARE @length int
DECLARE @hexstring char(16)
SELECT @charvalue = '0x'
SELECT @i = 1
SELECT @length = DATALENGTH (@binvalue)
SELECT @hexstring = '0123456789ABCDEF'
WHILE (@i <= @length)
BEGIN
DECLARE @tempint int
DECLARE @firstint int
DECLARE @secondint int
SELECT @tempint = CONVERT(int, SUBSTRING(@binvalue,@i,1))
SELECT @firstint = FLOOR(@tempint/16)
SELECT @secondint = @tempint - (@firstint*16)
SELECT @charvalue = @charvalue +
SUBSTRING(@hexstring, @firstint+1, 1) +
SUBSTRING(@hexstring, @secondint+1, 1)
SELECT @i = @i + 1
END

SELECT @hexvalue = @charvalue
GO

IF OBJECT_ID ('sp_help_revlogin') IS NOT NULL
DROP PROCEDURE sp_help_revlogin
GO
CREATE PROCEDURE sp_help_revlogin @login_name sysname = NULL AS
DECLARE @name sysname
DECLARE @type varchar (1)
DECLARE @hasaccess int
DECLARE @denylogin int
DECLARE @is_disabled int
DECLARE @PWD_varbinary varbinary (256)
DECLARE @PWD_string varchar (514)
DECLARE @SID_varbinary varbinary (85)
DECLARE @SID_string varchar (514)
DECLARE @tmpstr varchar (1024)
DECLARE @is_policy_checked varchar (3)
DECLARE @is_expiration_checked varchar (3)

DECLARE @defaultdb sysname

IF (@login_name IS NULL)
DECLARE login_curs CURSOR FOR

SELECT p.sid, <a href="http://p.name">p.name</a>, p.type, p.is_disabled, p.default_database_name, l.hasaccess, l.denylogin FROM
sys.server_principals p LEFT JOIN sys.syslogins l
ON ( <a href="http://l.name">l.name</a> = <a href="http://p.name">p.name</a> ) WHERE p.type IN ( 'S', 'G', 'U' ) AND <a href="http://p.name">p.name</a> &lt;&gt; 'sa'
ELSE
DECLARE login_curs CURSOR FOR

SELECT p.sid, <a href="http://p.name">p.name</a>, p.type, p.is_disabled, p.default_database_name, l.hasaccess, l.denylogin FROM
sys.server_principals p LEFT JOIN sys.syslogins l
ON ( <a href="http://l.name">l.name</a> = <a href="http://p.name">p.name</a> ) WHERE p.type IN ( 'S', 'G', 'U' ) AND <a href="http://p.name">p.name</a> = @login_name
OPEN login_curs

FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @type, @is_disabled, @defaultdb, @hasaccess, @denylogin
IF (@@fetch_status = -1)
BEGIN
PRINT 'No login(s) found.'
CLOSE login_curs
DEALLOCATE login_curs
RETURN -1
END
SET @tmpstr = '/* sp_help_revlogin script '
PRINT @tmpstr
SET @tmpstr = '** Generated ' + CONVERT (varchar, GETDATE()) + ' on ' + @@SERVERNAME + ' */'
PRINT @tmpstr
PRINT ''
WHILE (@@fetch_status &lt;&gt; -1)
BEGIN
IF (@@fetch_status &lt;&gt; -2)
BEGIN
PRINT ''
SET @tmpstr = '-- Login: ' + @name
PRINT @tmpstr
IF (@type IN ( 'G', 'U'))
BEGIN -- NT authenticated account/group

SET @tmpstr = 'CREATE LOGIN ' + QUOTENAME( @name ) + ' FROM WINDOWS WITH DEFAULT_DATABASE = [' + @defaultdb + ']'
END
ELSE BEGIN -- SQL Server authentication
-- obtain password and sid
SET @PWD_varbinary = CAST( LOGINPROPERTY( @name, 'PasswordHash' ) AS varbinary (256) )
EXEC sp_hexadecimal @PWD_varbinary, @PWD_string OUT
EXEC sp_hexadecimal @SID_varbinary,@SID_string OUT

-- obtain password policy state
SELECT @is_policy_checked = CASE is_policy_checked WHEN 1 THEN 'ON' WHEN 0 THEN 'OFF' ELSE NULL END FROM sys.sql_logins WHERE name = @name
SELECT @is_expiration_checked = CASE is_expiration_checked WHEN 1 THEN 'ON' WHEN 0 THEN 'OFF' ELSE NULL END FROM sys.sql_logins WHERE name = @name
SET @tmpstr = 'CREATE LOGIN ' + QUOTENAME( @name ) + ' WITH PASSWORD = ' + @PWD_string + ' HASHED, SID = ' + @SID_string + ', DEFAULT_DATABASE = [' + @defaultdb + ']'

IF ( @is_policy_checked IS NOT NULL )
BEGIN
SET @tmpstr = @tmpstr + ', CHECK_POLICY = ' + @is_policy_checked
END
IF ( @is_expiration_checked IS NOT NULL )
BEGIN
SET @tmpstr = @tmpstr + ', CHECK_EXPIRATION = ' + @is_expiration_checked
END
END
IF (@denylogin = 1)
BEGIN -- login is denied access
SET @tmpstr = @tmpstr + '; DENY CONNECT SQL TO ' + QUOTENAME( @name )
END
ELSE IF (@hasaccess = 0)
BEGIN -- login exists but does not have access
SET @tmpstr = @tmpstr + '; REVOKE CONNECT SQL TO ' + QUOTENAME( @name )
END
IF (@is_disabled = 1)
BEGIN -- login is disabled
SET @tmpstr = @tmpstr + '; ALTER LOGIN ' + QUOTENAME( @name ) + ' DISABLE'
END
PRINT @tmpstr
END

FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @type, @is_disabled, @defaultdb, @hasaccess, @denylogin
END
CLOSE login_curs
DEALLOCATE login_curs
RETURN 0
GO

How to clear MySQL Commands history

To clear MySQL commands history type the following in terminal or command prompt

cat /dev/null > ~/.mysql_history

Archive mails in Outlook 2011 Mac

Recently I was asked to find out a way to archive mails in Outlook 2011 for Mac and after a bit of struggle I finally managed it. Here’s how you do it:

In Outlook 2011 for Mac

Go to "Preferences"
Click on "General"
Then un-check the tick box "Hide On My Computer folders".

You should now see another account called "On My Computer" and you can create folders within to archive your mails locally with a simple drag and drop.

Importance of good documentation

As Database administrators we often find ourselves in position where there is lot of work to get done but not enough time to do it. And on top of this entire if we have to worry about documentation that is like asking for too much. So we decide to do it later when the job is done and things have somewhat calm down. But unfortunately that time never comes and little do we realize that negligence in documentation is going to cost us lot more than what we can possibly imagine.

Often documentation is neglected because the effect of such negligence is not immediately visible to us. Nevertheless there are some very real costs to not have proper documentation:

1. When there is no up-to-date documentation it is certain that troubleshooting of issues and providing timely support is going to take much longer than you expect.

2. We often forget how our own code worked what to speak of anyone else in our team.

3. When time is wasted in reinventing the wheel or trying to do something which could have been easily done if there was proper documentation costs money. The longer it takes to resolve an issue the more cost is incurred to our customers which indirectly affect our business.

4. Lack of documentation results in loss of knowledge. What happens when the expert who knows everything leaves the company?

It is crucial for administrators to think defensively and stop putting off documentation. Documentation must be given attention and priority because it really deserves it. Here are some of the many advantages of having good documentation:

1. You can react to incidents much faster when there are defined processes and procedures.

2. Anyone new who joins the team can be bought up to speed on things quickly.

3. Documentation helps us pick up our own work faster when we revisit it.

4. Documented solutions form a framework of ideas for people wanting to do similar things.

5. Delegation of responsibilities becomes much easier when there is good relevant documentation.

Services of outstanding DBAs will always be in demand and good DBAs knows how to maximize the available time they have to work on tasks in a way that adds value to business.

Get schedule tasks

The code below shows how you can get schedule tasks using powershell.

Code below works well for operating systems higher than 2003.

$servername = "<your server name or ip>"

$st = new-object -com ("schedule.service")
$st.connect($servername)
$rootfolder = $st.getfolder("\")
$tasks = $rootfolder.getTasks(0)
$tasks | select name, path, enabled, lastruntime, nextruntime | ft -Auto

For operating systems 2003 or lower use the following:

Schtasks.exe /query /s <servername> 

# Get help on schtasks.exe
schtasks /?

Writing Robocopy commands within powershell script

I was once asked how to write Robocopy commands within powershell scripts. Here is how we do it. Robocopy commands are written as is without single or double quotes. The code below also shows how to handle Robocopy errors. It uses Eventcreate, which is a windows command to create custom event in the Windows Events Logs. See How to create custom events for more information.

Robocopy "C:\temp" \\<ip address of destination>\c$\temp /R:2 /W:5 /MIR /Log+:D:\Logs\robolog.log

# Robocopy Error Handling  
if (errorlevel 16) {eventcreate /T Error /id 100 /SO ScheduleTask /L System /D " ***SERIOUS FATAL ERROR*** "}
if (errorlevel 15) {eventcreate /T Error /id 100 /SO ScheduleTask /L System /D " OKCOPY + FAIL + MISMATCHES + XTRA "}
if (errorlevel 14) {eventcreate /T Error /id 100 /SO ScheduleTask /L System /D " FAIL + MISMATCHES + XTRA "}
if (errorlevel 13) {eventcreate /T Error /id 100 /SO ScheduleTask /L System /D " OKCOPY + FAIL + MISMATCHES "}
if (errorlevel 12) {eventcreate /T Error /id 100 /SO ScheduleTask /L System /D " FAIL + MISMATCHES "}
if (errorlevel 11) {eventcreate /T Error /id 100 /SO ScheduleTask /L System /D " OKCOPY + FAIL + XTRA "}
if (errorlevel 10) {eventcreate /T Error /id 100 /SO ScheduleTask /L System /D " FAIL + XTRA "}
if (errorlevel 9) {eventcreate /T Error /id 100 /SO ScheduleTask /L System /D " OKCOPY + FAIL "}
if (errorlevel 8) {eventcreate /T Error /id 100 /SO ScheduleTask /L System /D " FAIL "}
if (errorlevel 7) {eventcreate /T Warning /id 100 /SO ScheduleTask /L System /D " OKCOPY + MISMATCHES + XTRA "}
if (errorlevel 6) {eventcreate /T Warning /id 100 /SO ScheduleTask /L System /D " MISMATCHES + XTRA "}
if (errorlevel 5) {eventcreate /T Warning /id 100 /SO ScheduleTask /L System /D " OKCOPY + MISMATCHES "}
if (errorlevel 4) {eventcreate /T Warning /id 100 /SO ScheduleTask /L System /D " MISMATCHES "}
if (errorlevel 3) {eventcreate /T Information /id 100 /SO ScheduleTask /L System /D " OKCOPY + XTRA "}
if (errorlevel 2) {eventcreate /T Information /id 100 /SO ScheduleTask /L System /D " XTRA "}
if (errorlevel 1) {eventcreate /T Information /id 100 /SO ScheduleTask /L System /D " OKCOPY "}
if (errorlevel 0) {eventcreate /T Information /id 100 /SO ScheduleTask /L System /D " No Change "}

Delete files older than n days

Here is a code to delete files older than N days where N refers number of days. In the code snippet below we are deleting files older than seven days.

$LastWrite = (Get-Date).AddDays(-7)  

Get-ChildItem "C:\temp" -Recurse | Where {$_.LastWriteTime -le $LastWrite} | foreach ($_) { Remove-Item $_.fullname}