locked
Encryption RRS feed

  • Question

  • When we used with encryption in our stored procedure or function script.Then,the text will be available to privileged users that can either access system tables over the DAC port or directly access database files. Also, users that can attach a debugger to the server process can retrieve the original procedure from memory at runtime.

    But, I am not able to see the text of Script of those stored procedure in which I used with encryption. Please clear this point how can we view the script of any stored procedure(with encryption)  on the DAC port if sa has full privilege to access the system tables


    Sunday, January 15, 2012 1:30 PM

Answers

All replies

  • http://searchwindevelopment.techtarget.com/tip/How-to-decrypt-encrypted-stored-procedures
    Balmukund Lakhani | Please mark solved if I've answered your question, vote for it as helpful to help other user's find a solution quicker
    --------------------------------------------------------------------------------
    This posting is provided "AS IS" with no warranties, and confers no rights.
    --------------------------------------------------------------------------------
    My Blog | Team Blog | @Twitter
    • Proposed as answer by Warwick Rudd Monday, January 16, 2012 11:12 AM
    Sunday, January 15, 2012 4:29 PM
  • Hi BalMukund , thanks for help but,

    It does not work on  stored procedure when I used with encryption key work on  AdventureWorks.dbo.uspGetBillOfMaterials

    Please advise me

    USE [AdventureWorks]
    GO

    /****** Object:  StoredProcedure [dbo].[uspGetBillOfMaterials]    Script Date: 01/15/2012 17:57:47 ******/
    SET ANSI_NULLS ON
    GO

    SET QUOTED_IDENTIFIER ON
    GO

    CREATE PROCEDURE [dbo].[uspGetBillOfMaterials]
    @StartProductID [int],
    @CheckDate [datetime]
    with encryption
    AS
    BEGIN
    SET NOCOUNT ON;
    WITH [BOM_cte]([ProductAssemblyID], [ComponentID], [ComponentDesc], [PerAssemblyQty], [StandardCost], [ListPrice], [BOMLevel], [RecursionLevel]) -- CTE name and columns
    AS (
    SELECT b.[ProductAssemblyID], b.[ComponentID], p.[Name], b.[PerAssemblyQty], p.[StandardCost], p.[ListPrice], b.[BOMLevel], 0 -- Get the initial list of components for the bike assembly
    FROM [Production].[BillOfMaterials] b
    INNER JOIN [Production].[Product] p
    ON b.[ComponentID] = p.[ProductID]
    WHERE b.[ProductAssemblyID] = @StartProductID
    AND @CheckDate >= b.[StartDate]
    AND @CheckDate <= ISNULL(b.[EndDate], @CheckDate)
    UNION ALL
    SELECT b.[ProductAssemblyID], b.[ComponentID], p.[Name], b.[PerAssemblyQty], p.[StandardCost], p.[ListPrice], b.[BOMLevel], [RecursionLevel] + 1 -- Join recursive member to anchor
    FROM [BOM_cte] cte
    INNER JOIN [Production].[BillOfMaterials] b
    ON b.[ProductAssemblyID] = cte.[ComponentID]
    INNER JOIN [Production].[Product] p
    ON b.[ComponentID] = p.[ProductID]
    WHERE @CheckDate >= b.[StartDate]
    AND @CheckDate <= ISNULL(b.[EndDate], @CheckDate)
    )
    SELECT b.[ProductAssemblyID], b.[ComponentID], b.[ComponentDesc], SUM(b.[PerAssemblyQty]) AS [TotalQuantity] , b.[StandardCost], b.[ListPrice], b.[BOMLevel], b.[RecursionLevel]
    FROM [BOM_cte] b
    GROUP BY b.[ComponentID], b.[ComponentDesc], b.[ProductAssemblyID], b.[BOMLevel], b.[RecursionLevel], b.[StandardCost], b.[ListPrice]
    ORDER BY b.[BOMLevel], b.[ProductAssemblyID], b.[ComponentID]
    OPTION (MAXRECURSION 25)
    END;

    GO




    Monday, January 16, 2012 3:23 PM
  • Monday, January 16, 2012 3:28 PM
  • Thanks Jon Gugul.
    Monday, January 16, 2012 4:10 PM
  • Monday, January 16, 2012 4:11 PM