How to Get/Crack a Lost Excel Macro PW? - 2003 and below

http://stackoverflow.com/questions/1026483/is-there-a-way-to-crack-the-password-on-an-excel-vba-project


Yes there is, as long as you are using a .xls format spreadsheet (the default for Excel up to 2003). For Excel 2007 onwards, the default is .xlsx, which is a fairly secure format, and this method will not work.

As Treb says, it's a simple comparison, so one method is simply to swap out the password entry in the file using a hex editor (see What is a good Windows hex editor?). Step by step example:

1. Create a new simple excel file.
2. In the VBA part, set a simple password (say - 1234).
3. Save the file and exit. Then check the file size - see Stewbob's gotcha
4. Open the file you just created with a hex editor.
5.

Copy the lines starting with the following keys:

CMG=....
DPB=...
GC=...

6.

FIRST BACKUP the excel file you don't know the VBA password for, then open it with your hex editor, and paste the above copied lines from the dummy file.
7. save the excel file and exit.
8. Now, open the excel file you need to see the VBA code in. The password for the VBA code will simply be 1234 (as in the example I'm showing here).

Proper Case t-sql

ALTER FUNCTION ProperCase(@Text VARCHAR (8000))
RETURNS VARCHAR(8000)
AS
BEGIN
/***************************************************

2011-08-24 10:44:23.503

Source: http://weblogs.sqlteam.com/jeffs/archive/2007/03/09/60131.aspx
***************************************************/


DECLARE @Reset BIT
DECLARE @Ret VARCHAR(8000)
DECLARE @i INT
DECLARE @c CHAR (1)

SELECT @Reset = 1, @i=1, @Ret = '';

WHILE (@i <= LEN (@Text))
SELECT @c=
SUBSTRING (@Text,@i,1)
, @Ret = @Ret
+ CASE
WHEN @Reset=1
THEN UPPER(@c)
ELSE LOWER(@c)
END
, @Reset = CASE
WHEN @c LIKE '[a-zA-Z]'
THEN 0
ELSE 1
END
, @i = @i +1
RETURN @Ret

END

Undocumented strored procedure sp_MSgetversion will gives version information of sql server

EXEC master..sp_MSgetversion

http://beyondrelational.com/justlearned/posts/496/about-the-undocumented-strored-procedure-spmsgetversion.aspx?utm_source=brnewsletter&utm_medium=email&utm_campaign=2011Aug19

FYI - Fn for Tuning Excel Dates into T-SQL Dates (ISO 8601 standard format)

=TEXT(B5,"yyyy-mm-dd")

i.e.


07/14/11 becomes 2011-07-14
07/22/11 becomes 2011-07-22
07/28/11 becomes 2011-07-28
08/05/11 becomes 2011-08-05
08/11/11 becomes 2011-08-11
08/19/11 becomes 2011-08-19
08/25/11 becomes 2011-08-25
09/02/11 becomes 2011-09-02



Credit: http://michiel.wordpress.com/2010/03/26/convert-excel-date-value-to-sql-date/

DB Mail - profile name is not valid

Use this parameterized syntax:

EXEC msdb.dbo.sp_send_dbmail @recipients = @To
, @profile_name = 'Decision Support Services'
, @subject = @subject
, @body = @body
, @body_format = 'HTML'

Read Only DB User with DDL per Schema

USE [DB]
GO
GRANT VIEW DEFINITION ON SCHEMA::[dbo] TO [domain\user]
GO

Read Only DB User with DDL

USE myDB
GO
GRANT VIEW DEFINITION DATABASE::myDB TO [DOMAIN\USER]