Thursday, October 30, 2008

All Your Command (CMD) Prompts Are Belong To Us

Ever typed SET to display all the useful environment variables windows has?

You can add those environment variables to your command prompt permanently. And No, Billy, this won't work by simply editing System Properties, so read on...

Four files and the PSEXEC.EXE utility are needed - save all 4 files in the same directory in a share(such as \\server\share ) to prepare to deploy your groovy command prompt on every server under your influence.

I changed the command prompt to better keep track of which server and which credentials are in use, and added a timestamp.

CMDPROMPT.BAT - contains the prompt with your preferred environment variables and gets copied to %windir%
:: c:\windows\cmdprompt.bat
@echo off
prompt $_[%ComputerName%\%Username%] $t$_$p$g

CMDPROMPT.REG - get registered on the target machine(s) to invoke CMDPROMPT.BAT when CMD.EXE is executed
Windows Registry Editor Version 5.00

[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Command Processor]
"AutoRun"="%windir%\\cmdprompt.bat"


CMDPROMPT-INSTALL.CMD - the installer script - important for deploying the prompt to multiple machines
:: \\server\share\cmdprompt-install.cmd
@echo off
copy /Y \\server\share\cmdprompt.??? %windir%\
regedit /s %windir%\cmdprompt.reg


SERVERLIST.TXT - a list of target machines(one per line) that will receive the newly minted command prompt goodness.
Eriador
Gondor
GreyHavens
Minastirith
Mordor
Rivendell
Rohan
Bullwinkle
Rocky
Elaine
George
Jerry
Challenger
Cubs
Hindenburg
SpruceGoose
Titanic
TowerofPiza

Now to install! Open up a command prompt and cd to the directory containing the SERVERLIST.TXT , then run the following:
psexec @SERVERLIST.TXT -c -u DOMAIN\%username% \\server\share\cmdprompt-install.cmd


Login to some of these target servers and enjoy the utility of the improved prompt.

Tuesday, October 28, 2008

sp_helpprotect2 - Version of sp_helpprotect stored proc that Returns Valid T-sql Permissions

One common task with SQL Server is viewing and modifying table permissions.

sp_helprotect can spit out some very helpful reports, but the returned SQL does not comprise valid T-SQL statements unless the version of sp_helprotect in the master database is modified a bit. This modified version is called sp_helprotect2, and marks itself as a system object. Thanks to wzard on Experts Exchange for this syntax.


/****** Object: StoredProcedure [dbo].[sp_helprotect2]
by: wzard http://www.experts-exchange.com/Database/Miscellaneous/Q_21694613.html

Edited and Reposted by Lars Rasmussen on http://larsrasmussen.blogspot.com/
******/

SET
NOCOUNT ON
GO


PRINT
'Using Master database'
USE
master
GO

PRINT
'Checking if procedure already exists in master database...'
IF
(SELECT OBJECT_ID('sp_helprotect2','P')) IS NOT NULL --means, the procedure already exists
BEGIN
PRINT 'Procedure already exists - dropping it.'
DROP
PROC sp_helprotect2
END
GO


CREATE
PROCEDURE [dbo].[sp_helprotect2]
@name ncharacter varying(776) = NULL
,
@username sysname = NULL
,
@grantorname sysname = NULL
,
@permissionarea character varying(10) = 'o s'
as
BEGIN

/********
Explanation of the parms...
---------------------------
@name: Name of [Owner.]Object and Statement; meaning
for sysprotects.id and sysprotects.action at the
same time; thus see parm @permissionarea.
Examples- 'user2.tb' , 'CREATE TABLE', null

@username: Name of the grantee (for sysprotects.uid).
Examples- 'user2', null

@grantorname: Name of the grantor (for sysprotects.grantor).
Examples- 'user2' --Would prevent report rows which would
-- have 'dbo' as grantor.

@permissionarea: O=Object, S=Statement; include all which apply.
Examples- 'o' , ',s' , 'os' , 'so' , 's o' , 's,o'
GeneMi
********/
Set nocount on
Declare
@vc1 sysname
,
@Int1 integer
Declare
@charMaxLenOwner character varying(11)
,
@charMaxLenObject character varying(11)
,
@charMaxLenGrantee character varying(11)
,
@charMaxLenGrantor character varying(11)
,
@charMaxLenAction character varying(11)
,
@charMaxLenColumnName character varying(11)
Declare
@OwnerName sysname
,
@ObjectStatementName sysname

/* Perform temp table DDL here to minimize compilation costs*/
CREATE Table #t1_Prots
( Id int Null
,Type1Code char(6) collate database_default NOT Null
,
ObjType char(2) collate database_default Null
,ActionName varchar(20) collate database_default Null
,
ActionCategory char(2) collate database_default Null
,
ProtectTypeName char(10) collate database_default Null
,Columns_Orig varbinary(32) Null
,OwnerName sysname collate database_default NOT Null
,
ObjectName sysname collate database_default NOT Null
,
GranteeName sysname collate database_default NOT Null
,
GrantorName sysname collate database_default NOT Null
,ColumnName sysname collate database_default Null
,
ColId smallint Null
,Max_ColId smallint Null
,
All_Col_Bits_On tinyint Null
,
new_Bit_On tinyint Null ) -- 1=yes on

/* Check for valid @permissionarea */
Select
@permissionarea = upper( isnull(@permissionarea,'?') )
IF ( charindex('O',@permissionarea) <= 0
AND charindex('S',@permissionarea) <= 0)
begin
raiserror(15300,-1,-1 ,@permissionarea,'o,s')
return
(1)
end
select @vc1 = parsename(@name,3)
/* Verified db qualifier is current db*/
IF
(@vc1 is not null and @vc1 <> db_name())
begin
raiserror(15302,-1,-1) --Do not qualify with DB name.
return
(1)
end
/* Derive OwnerName and @ObjectStatementName*/
select
@OwnerName = parsename(@name, 2)
,@ObjectStatementName = parsename(@name, 1)
IF (@ObjectStatementName is NULL and @name is not null)
begin
raiserror(15253,-1,-1,@name)
return
(1)
end
/* Copy info from sysprotects for processing */
IF
charindex('O',@permissionarea) > 0
begin
/* Copy info for objects */
INSERT
#t1_Prots
(
Id
,Type1Code
,ObjType
,
ActionName
,
ActionCategory
,
ProtectTypeName
,Columns_Orig
,
OwnerName
,
ObjectName
,
GranteeName
,GrantorName
,
ColumnName
,
ColId
,Max_ColId
,
All_Col_Bits_On
,
new_Bit_On )
/* 1Regul indicates action can be at column level,
2Simpl indicates action is at the object level */
SELECT
id
,case
when columns is null then '2Simpl'
else
'1Regul'
end
,Null
,
val1.name
,
'Ob'
,
val2.name
,columns
,
user_name(objectproperty( id, 'ownerid' ))
,
object_name(id)
,
user_name(uid)
,user_name(grantor)
,
case
when columns is null then '.'
else
Null
end
,-
123
,Null
,Null

,Null
FROM sysprotects sysp
,master.dbo.spt_values val1
,
master.dbo.spt_values val2
where (@OwnerName is null or user_name(objectproperty( id, 'ownerid' )) = @OwnerName)
and
(@ObjectStatementName is null or object_name(id) = @ObjectStatementName)
and
(@username is null or user_name(uid) = @username)
and
(@grantorname is null or user_name(grantor) = @grantorname)
and
val1.type = 'T'
and
val1.number = sysp.action
and
val2.type = 'T' --T is overloaded.
and
val2.number = sysp.protecttype
and
sysp.id != 0

IF EXISTS (SELECT * From #t1_Prots)
begin
UPDATE #t1_Prots set ObjType = ob.xtype
FROM
sysobjects ob
WHERE
ob.id = #t1_Prots.Id

UPDATE #t1_Prots
set
Max_ColId = (select max(colid) from syscolumns sysc
where #t1_Prots.Id = sysc.id) -- colid may not consecutive if column dropped
where Type1Code = '1Regul'

/* First bit set indicates actions pretains to new columns. (i.e. table-level permission)
Set new_Bit_On accordinglly */
UPDATE #t1_Prots SET new_Bit_On =
CASE
convert(int,substring(Columns_Orig,1,1)) & 1
WHEN 1 then 1
ELSE
0
END
WHERE
ObjType <> 'V' and Type1Code = '1Regul'

/* Views don't get new columns */
UPDATE
#t1_Prots set new_Bit_On = 0
WHERE
ObjType = 'V'

/* Indicate enties where column level action pretains to all
columns in table All_Col_Bits_On = 1 */
UPDATE #t1_Prots set All_Col_Bits_On = 1
where
#t1_Prots.Type1Code = '1Regul'
and
not exists
(select *
from
syscolumns sysc, master..spt_values v
where
#t1_Prots.Id = sysc.id and sysc.colid = v.number
and
v.number <= Max_ColId -- column may be dropped/added after Max_ColId snap-shot
and
v.type = 'P' and
/* Columns_Orig where first byte is 1 means off means on and on mean off
where first byte is 0 means off means off and on mean on */
case convert(int,substring(#t1_Prots.Columns_Orig, 1, 1)) & 1
when 0 then convert(tinyint, substring(#t1_Prots.Columns_Orig, v.low, 1))
else
(~convert(tinyint, isnull(substring(#t1_Prots.Columns_Orig, v.low, 1),0)))
end & v.high = 0)

/* Indicate entries where column level action pretains to
only some of columns in table All_Col_Bits_On = 0*/
UPDATE #t1_Prots set All_Col_Bits_On = 0
WHERE
#t1_Prots.Type1Code = '1Regul'
and
All_Col_Bits_On is null

Update #t1_Prots
set
ColumnName =
case
when All_Col_Bits_On = 1 and new_Bit_On = 1 then '(All+New)'
when
All_Col_Bits_On = 1 and new_Bit_On = 0 then '(All)'
when
All_Col_Bits_On = 0 and new_Bit_On = 1 then '(New)'
end
from
#t1_Prots
where
ObjType IN ('S ' ,'U ', 'V ')
and
Type1Code = '1Regul'
and
NOT (All_Col_Bits_On = 0 and new_Bit_On = 0)

/* Expand and Insert individual column permission rows */
INSERT
into #t1_Prots
(Id
,
Type1Code
,
ObjType
,
ActionName
,ActionCategory
,
ProtectTypeName
,
OwnerName
,
ObjectName
,GranteeName
,
GrantorName
,
ColumnName
,
ColId )
SELECT prot1.Id
,'1Regul'
,
ObjType
,
ActionName
,ActionCategory
,
ProtectTypeName
,
OwnerName
,
ObjectName
,GranteeName
,
GrantorName
,
col_name ( prot1.Id ,val1.number )
,
val1.number
from #t1_Prots prot1
,master.dbo.spt_values val1
,
syscolumns sysc
where prot1.ObjType IN ('S ' ,'U ' ,'V ')
and prot1.All_Col_Bits_On = 0
and
prot1.Id = sysc.id
and
val1.type = 'P'
and
val1.number = sysc.colid
and

case
convert(int,substring(prot1.Columns_Orig, 1, 1)) & 1
when 0 then convert(tinyint, substring(prot1.Columns_Orig, val1.low, 1))
else
(~convert(tinyint, isnull(substring(prot1.Columns_Orig, val1.low, 1),0)))
end & val1.high <> 0
delete from #t1_Prots
where ObjType IN ('S ' ,'U ' ,'V ')
and All_Col_Bits_On = 0
and
new_Bit_On = 0
end
end

/* Handle statement permissions here*/
IF
(charindex('S',@permissionarea) > 0)
begin

/* All statement permissions are 2Simpl */
INSERT #t1_Prots
( Id
,Type1Code
,
ObjType
,
ActionName
,ActionCategory
,
ProtectTypeName
,
Columns_Orig
,
OwnerName
,ObjectName
,
GranteeName
,
GrantorName
,
ColumnName
,ColId
,
Max_ColId
,
All_Col_Bits_On
,
new_Bit_On )
SELECT id
,'2Simpl'
,Null

,
val1.name
,'St'
,
val2.name
,
columns
,
'.'
,'.'
,
user_name(sysp.uid)
,
user_name(sysp.grantor)
,
'.'
,-
123
,Null
,Null

,Null
FROM sysprotects sysp
,master.dbo.spt_values val1
,
master.dbo.spt_values val2
where (@username is null or user_name(sysp.uid) = @username)
and (@grantorname is null or user_name(sysp.grantor) = @grantorname)
and
val1.type = 'T'
and
val1.number = sysp.action
and
(@ObjectStatementName is null or val1.name = @ObjectStatementName)
and
val2.number = sysp.protecttype
and
val2.type = 'T'
and
sysp.id = 0
end

IF NOT EXISTS (SELECT * From #t1_Prots)
begin
raiserror(15330,-1,-1)
return
(1)
end

/* Calculate dynamic display col widths */
SELECT

@charMaxLenOwner =
convert ( varchar, max(datalength(OwnerName)))
,@charMaxLenObject =
convert ( varchar, max(datalength(ObjectName)))
,@charMaxLenGrantee =
convert ( varchar, max(datalength(GranteeName)))
,@charMaxLenGrantor =
convert ( varchar, max(datalength(GrantorName)))
,@charMaxLenAction =
convert ( varchar, max(datalength(ActionName)))
,@charMaxLenColumnName =
convert ( varchar, max(datalength(ColumnName)))
from #t1_Prots


/* Output the report */

create
table #mytmp ([owner] varchar(255), [object] varchar(255), grantee varchar(255), grantor varchar(255), protecttype varchar(255), [action] varchar(255), [column] varchar(255))
EXECUTE
(
'Set nocount off
INSERT INTO #mytmp
SELECT ''Owner'' = substring (OwnerName ,1 ,'
+ @charMaxLenOwner + ')
,''Object'' = substring (ObjectName ,1 ,' + @charMaxLenObject + ')
,''Grantee'' = substring (GranteeName ,1 ,' + @charMaxLenGrantee + ')
,''Grantor'' = substring (GrantorName ,1 ,' + @charMaxLenGrantor + ')
,''ProtectType''= ProtectTypeName
,''Action'' = substring (ActionName ,1 ,' + @charMaxLenAction + ')
,''Column'' = substring (ColumnName ,1 ,' + @charMaxLenColumnName + ')
from #t1_Prots
order by
ActionCategory
,Owner ,Object
,Grantee ,Grantor
,ProtectType ,Action
,ColId --Multiple -123s ( &lt0 ) possible

Set nocount on'

)

select
[protecttype] + ' ' + [action] + ' on [' + [object] + '] to [' + [grantee] + ']' from [#mytmp]
Return
(0) -- sp_helprotect2
END

GO

PRINT
'Procedure created.'
GO

--Mark procedure as system object

EXEC
sp_MS_marksystemobject sp_helprotect2
GO

Tuesday, October 14, 2008

Create Windows Shares from the Command Line using RMTSHARE.EXE

RMTSHARE.EXE from one of the Windows NT Resource kits makes quick work of creating shares.

Here's an example of creating a hidden, read-only share accessible only by the fictional AD group 'DOMAIN\AdminsSQL':
rmtshare \\SQLSERVERNAME\BACKUPS-RO$=E:\BACKUPS /GRANT "DOMAIN\AdminsSQL":r


Want write(change) access but still with a hidden share? Use this syntax:
rmtshare \\SQLSERVERNAME\BACKUPS-RW$=E:\BACKUPS /GRANT "DOMAIN\AdminsSQL":c


Remove the dollar sign from the end of the share name if you want the share to be visible.

Running RMTSHARE.EXE against a \\SERVERNAME with no parameters shows all the shares(including the hidden shares) available, like so:
C:\>rmtshare \\SQLSERVERNAME

Share name Resource Remark

-------------------------------------------------------------------------------
IPC$ Remote IPC
C$ C:\ Default share
X$ X:\ Default share
ADMIN$ C:\WINDOWS Remote Admin
BACKUPS-RO$ E:\BACKUPS Shared by remote command.
BACKUPS-RW$ E:\BACKUPS Shared by remote command.
D$ D:\ Default share
L$ L:\ Default share
E$ E:\ Default share
The command completed successfully.


Tested in Vista, Server 2003, Server 2000. Let me know if you find a version of Windows incompatible with RMTSHARE.EXE!

Friday, October 10, 2008

'Copy as Path' - Vista's Best Feature

Arguably one of the best features in Vista for those who live in Explorer with files and directories appears by holding down the Shift key when right-clicking a file or folder:



Copy as Path copies the full path of the file or folder selected as text to the clipboard that can then be pasted in email, in code or at the command line.

The full path gets copied as a plain text string, surrounded by quotes to handle any spaces that might be contained in the path/filename.

Tuesday, October 07, 2008

Move TEMPDB to a Separate Drive

It can be beneficial to move TEMPDB to an alternate location for improved performance. I've run across installations where tempdb was located on the OS drive with autogrow enabled and the drive nearly ran out of space!

This change requires a restart of the SQL Server Service(MSSQLSERVER).

Here's an example in SQL Server 2005 of moving TEMPDB to the (T:) drive with one data file per CPU(four CPUs). On the box used in this example, the (T:) drive is on a SAN.

SELECT name, physical_name
FROM
sys.master_files
WHERE
database_id = DB_ID('tempdb');
GO

ALTER
DATABASE tempdb
MODIFY
FILE (NAME = tempdev, FILENAME = 'T:\SQLTEMPDB\tempdb.mdf');
GO
ALTER
DATABASE tempdb
MODIFY
FILE (NAME = templog, FILENAME = 'T:\SQLTEMPDB\templog.ldf');
GO

ALTER
DATABASE [tempdb] ADD FILE ( NAME = N'tempdb1', FILENAME = N'T:\SQLTEMPDB\tempdb1.ndf' , SIZE = 100MB , FILEGROWTH = 50MB )
GO
ALTER
DATABASE [tempdb] ADD FILE ( NAME = N'tempdb2', FILENAME = N'T:\SQLTEMPDB\tempdb2.ndf' , SIZE = 100MB , FILEGROWTH = 50MB )
GO
ALTER
DATABASE [tempdb] ADD FILE ( NAME = N'tempdb3', FILENAME = N'T:\SQLTEMPDB\tempdb3.ndf' , SIZE = 100MB , FILEGROWTH = 50MB )
GO
ALTER
DATABASE [tempdb] MODIFY FILE ( NAME = N'tempdev', SIZE = 100MB , FILEGROWTH = 50MB )
GO
ALTER
DATABASE [tempdb] MODIFY FILE ( NAME = N'templog', SIZE = 10MB , FILEGROWTH = 10MB )
GO

SELECT
name, physical_name
FROM
sys.master_files
WHERE
database_id = DB_ID('tempdb');
GO

--restart needed to affect changes