Vista : The great disk hog!

Useful links for those who are repapdly losing disk space to vista...

Manual tips for reclaiming space

Vista System Junk Cleaner tool

SPN - Service Principal Name Registration

SPN is a attribute of an Active Directory computer account. It ties together the machine name, a service account and the port number.

We need a SPN established to allow Kerberos authentication with SQL.
Kerberos allows pass through authentication eg user > web server > sql server

You want to create an spn for a sql instance when the account that sql server runs from does not have rights to create an SPN (most cases really, in secure environments).

To manually configure an SPN you need 'setspn', which is part of 'Windows 2000 / 2003 Resource Kit'. Once installed >
C:\Program Files\Resource Kit>setspn
Usage: setspn [switches data] computername Where "computername" can be the name or domain\name

Switches: -R = reset HOST ServicePrincipalName Usage: setspn -R computername
-A = add arbitrary SPN Usage: setspn -A SPN computername
-D = delete arbitrary SPN Usage: setspn -D SPN computername
-L = list registered SPNs Usage: setspn [-L] computername

Examples:
setspn -R daserver1 It will register SPN "HOST/daserver1" and "HOST/{DNS of daserver1}"
setspn -A http/daserver daserver1 It will register SPN "http/daserver" for computer "daserver1"
setspn -D http/daserver daserver1 It will delete SPN "http/daserver" for computer "daserver1"

Adding a SPN > setspn - A SERVICENAME/FQDN serviceaccount
C:\Program Files\Resource Kit>setspn -A MSSQLSvc/livesql001.domain.co.uk SqlServiceAccount
Registering ServicePrincipalNames for CN=SqlServiceAccount,OU=Service Accounts,DC=domain,DC=co,DC=uk MSSQLSvc/livesql001.domain.co.uk Updated object
C:\Program Files\Resource Kit>setspn -A MSSQLSvc/livesql001.domain.co.uk:1433 SqlServiceAccount Registering ServicePrincipalNames for CN=SqlServiceAccount,OU=Service Accounts,DC=domain,DC=co,DC=uk MSSQLSvc/livesql001.domain.co.uk:1433 Updated object

Checking an SPN > setspn -l serviceaccount
C:\Program Files\Resource Kit>setspn -l SqlServiceAccount
Registered ServicePrincipalNames for CN=SqlServiceAccount,OU=Service Accounts,DC=domain,DC=co,DC=uk: MSSQLSvc/livesql001.domain.co.uk:1433 MSSQLSvc/livesql001.domain.co.uk MSSQLSvc/livesql002.domain.co.uk:1433 MSSQLSvc/testsql002.domain.co.uk:1433 MSSQLSvc/livesql002:1433 MSSQLSvc/devsql001.domain.co.uk:1433

You can also check an spn via adsi edit (Active Directory editor tool).

Technet explanation on SPNs >
http://technet2.microsoft.com/windowsserver/en/library/8127f5ed-4e05-4822-bfa9-402ceede47441033.mspx?mfr=true

So, SPN ties together -

• machine name
• port number
• account name

for active directory.

Grant Execute to all Stored Procedures

Solution 1 : Dynamic SQL -

SELECT 'GRANT EXECUTE ON ' + NAME + ' TO LoginName' -- Replace LoginName with the name of your new Login
FROM SYSOBJECTS
WHERE TYPE = 'P'
AND LEFT(NAME,2) <> 'sp' -- system procs
AND LEFT(NAME,2) <> 'dt' -- VSS procs

Solution 2 : Procedure -

CREATE PROCEDURE USP_gen_CreateGrants AS
DECLARE @ExecSQL varchar(100)
DECLARE curGrants CURSOR FOR

SELECT 'GRANT EXECUTE ON ' + NAME + ' TO MyLogin' -- Replace MyLogin with the name of your new Login
FROM SYSOBJECTS
WHERE TYPE = 'P'
AND LEFT(NAME,2) <> 'sp' -- system procs
AND LEFT(NAME,2) <> 'dt' -- VSS procs

OPEN curGrants
FETCH NEXT FROM curGrants
INTO @ExecSQL

WHILE @@FETCH_STATUS = 0
BEGIN
Exec(@ExecSQL)
IF @@ERROR <> 0
BEGIN
RETURN 1 -- return 1 if there is an error
END
Print @ExecSQL
FETCH NEXT FROM curGrants INTO @ExecSQL
END

CLOSE curGrants
DEALLOCATE curGrants

SQL Server Default Port Numbers

135 – SQL Debugger

1433 – SQL Server

1434 – SQL Browser

2383 – Analysis Services

4022 – Service Broker

Useful tool : WindirStat

A funky tool to help visualise and hence clean up hard disk bloat. It proved very helpful in finding over 5GB of rubbish left in c:\windows\csc by a colleague who used 'Client Side Caching'.

The application even features an animated Pacman character during disk analysis :)

Download it for free @ http://windirstat.info/

Blogger : Feeling Lucky Widget

A tidy piece of script for adding a link to a random post on your blogger site.
I've implemented it over there on the right >>>

http://phydeauxredux.googlepages.com/Blogger-Feeling-Lucky.html

Datetime manipulation Gotcha

Spot the difference -

SELECT CONVERT(DATETIME,'2007-12-03 11:51:00.000',21)

2007-12-03 11:51:00.000


PRINT CONVERT(DATETIME,'2007-12-03 11:51:00.000',21)

Dec 3 2007 11:51AM