Recently I needed to setup a SQL Server box so it had access to a mapped drive to support a legacy application. I created the below stored procedure, which utilises the subst command. to get this done.

CREATE PROCEDURE usp_mapDDrive
AS
BEGIN
	EXEC master.dbo.xp_cmdshell 'Subst d: c:\', no_output;
END
GO

I needed a way of ensuring this mapped drive was always available to SQL Server. After considering various ways of doing this I settled with sp_procoption. This is a neat little system stored procedure that you can use to execute a user proc at startup. Setting this up is easy.

exec sp_procoption @ProcName = usp_mapDDrive,
     @OptionName = 'STARTUP',
     @OptionValue = 'ON';

Turning it off again is simple.

exec sp_procoption @ProcName = usp_mapDDrive,
     @OptionName = 'STARTUP',
     @OptionValue = 'OFF';

Two caveats; you must create your stored procedure in the master database and it cannot contain input or output parameters.