SQL Server Performance

SP For Grant Permissions at once

Discussion in 'Contribute Your SQL Server Scripts' started by patel_mayur, Jul 1, 2005.

  1. patel_mayur New Member

    Following is the SP that grant permissions to the user at once. Procedure expects two parameters ( DatabaseName & UserName ). One can easily modify the SP to grant the desired permission to the user.SP uses the undocumented XP ( XP_EXECRESULTSET )

    So Here we go !!!

    CREATE PROCEDURE ProcGrantPermission
    (
    @p_DbNameVARCHAR(100),
    @p_UserNameVARCHAR(100)
    )
    AS
    BEGIN

    DECLARE @v_GrantString VARCHAR(4000)

    -- Grant Select On Tables
    SET @v_GrantString = 'Select ' + '''' + 'Grant Select On [' + '''' + ' + Name + ' + '''' + '] To ' + @p_UserName + '''' + ' From Sysobjects Where Type = ' + '''' + 'U' + ''''
    EXEC Master..XP_EXECRESULTSET @v_GrantString , @p_DbName

    -- Grant Execute On Procedures and Functions
    SET @v_GrantString = 'Select ' + '''' + 'Grant Execute On [' + '''' + ' + Name + ' + '''' + '] To ' + @p_UserName + '''' + ' From Sysobjects Where Type = ' + '''' + 'P' + '''' + ' OR Type = ' + '''' + 'FN' + ''''
    EXEC Master..XP_EXECRESULTSET @v_GrantString , @p_DbName

    END
  2. ranjitjain New Member

    Hi mayur,
    code is good but
    do you feel using undocument XP is good, as MS says they can simply ignore these undocumented xp in future release....
  3. Madhivanan Moderator

    Yes it should be noted whether the same will function in 2005 version


    Madhivanan

    Failing to plan is Planning to fail
  4. FrankKalis Moderator

    With the little help of a temporary table this can easily be rewritten without the undocumented proc. Furthermore I wouldn't query the system tables. I think the same can be achieved with the INFORMATION_SCHEMA views.

    --
    Frank Kalis
    Microsoft SQL Server MVP
    http://www.insidesql.de
    Ich unterstütze PASS Deutschland e.V. http://www.sqlpass.de)

Share This Page