SQL Server Performance

help with SP_EXECUTESQL

Discussion in 'SQL Server 2005 General Developer Questions' started by shankbond, Feb 7, 2010.

  1. shankbond New Member

    Hi,
    I need help with proc sp_executesql
    The following script when run shows an error
    I am not able to debug the script or may be I am using it incorrectly?
    Please help
    CREATE TABLE #permissiontable(
    nameofobject NVARCHAR(50),
    permission_type NVARCHAR(50),
    username NVARCHAR(50)
    );
    CREATE TABLE #objects(
    objectname NVARCHAR(50)
    );
    DELETE FROM #permissiontable
    DELETE FROM #objects

    INSERT INTO #objects
    SELECT name AS [Object Name] FROM sys.objects
    WHERE type IN('U', 'P', 'V', 'FN')

    DECLARE @objectname NVARCHAR(50)
    DECLARE @sqlstr NVARCHAR(1000)


    DECLARE CR__permission CURSOR FOR
    SELECT objectname FROM #objects
    OPEN CR__permission
    FETCH NEXT FROM CR__permission INTO @objectname

    WHILE(@@FETCH_STATUS=0)
    BEGIN
    SET @sqlstr=
    N'EXECUTE AS USER=''username''
    INSERT INTO #permissiontable (nameofobject, permission_type)
    SELECT entity_name, permission_name FROM fn_my_permissions
    ('+@objectname+',''OBJECT'')
    WHERE subentity_name='''' AND permission_name IN
    (''SELECT'', ''INSERT'', ''UPDATE'', ''DELETE'')
    UPDATE #permissiontable SET username=''username''
    REVERT;'
    EXECUTE SP_EXECUTESQL @sqlstr



    FETCH NEXT FROM CR__permission INTO @objectname
    END
    CLOSE CR__permission
    DEALLOCATE CR__permission
    SELECT * FROM #permissiontable

  2. preethi Member

    Hi,
    What is the error you are getting?
  3. preethi Member

    I tried your code with little change to reflect my environment and it is working. Other than changing the user name, the other change is referring the object with schema name.
    CREATE TABLE #permissiontable(
    nameofobject NVARCHAR(50),
    permission_type NVARCHAR(50),
    username NVARCHAR(50)
    );
    CREATE TABLE #objects(
    objectname NVARCHAR(50)
    );
    DELETE FROM #permissiontable
    DELETE FROM #objects

    INSERT INTO #objects
    SELECT object_schema_name(object_id) +'.'+ name AS [Object Name] FROM sys.objects
    WHERE type IN('U', 'P', 'V', 'FN')
    Print 'Code execution starts'

    DECLARE @objectname NVARCHAR(50)
    DECLARE @sqlstr NVARCHAR(1000)

    DECLARE CR__permission CURSOR FOR
    SELECT objectname FROM #objects
    OPEN CR__permission
    FETCH NEXT FROM CR__permission INTO @objectname


    WHILE(@@FETCH_STATUS=0)
    BEGIN
    SET @sqlstr=
    N'EXECUTE AS USER=''User_Dummy''
    INSERT INTO #permissiontable (nameofobject, permission_type)
    SELECT entity_name, permission_name FROM fn_my_permissions
    ('''+@objectname+''',''OBJECT'')
    WHERE subentity_name='''' AND permission_name IN
    (''SELECT'', ''INSERT'', ''UPDATE'', ''DELETE'')
    UPDATE #permissiontable SET username=''User_Dummy''
    REVERT;'
    EXECUTE SP_EXECUTESQL @sqlstr



    FETCH NEXT FROM CR__permission INTO @objectname
    END
    CLOSE CR__permission
    DEALLOCATE CR__permission
    SELECT * FROM #permissiontable

    drop TABLE #permissiontable
    drop table #objects

  4. shankbond New Member

    Hi Preethi,
    [quote user="preethi"]('''+@objectname+''',''OBJECT'')[/quote]
    I was getting an error around this line, I was surrounding @objectname with 2('), but I still do not understand Why You used three of them, can You please
    explain!
    Also if possible Can You please tell me how to test the object Table In tempdb just like OBJECT_ID('#objects',N'U')
    But This does not works.
  5. shankbond New Member

    [quote user="shankbond"]
    Also if possible Can You please tell me how to test the object Table In tempdb just like OBJECT_ID('#objects',N'U')
    But This does not works.
    [/quote]
    Thanks but I found the solution to it:
    SELECT * FROM tempdb.dbo.sysobjects
    WHERE ID = OBJECT_ID(N'tempdb..#objects')
  6. preethi Member

    Good to hear. Now I am just wondering how that code worked on my database server..[:cool:]

Share This Page