scripting-permissions-in-sql-server

From:
http://www.gringod.com/2007/02/27/scripting-permissions-in-sql-server


This is just incase this post is pulled down.
Really useful!!!!

--Written By Bradley Morris
--In Query Analyzer be sure to go to
--Query -> Current Connection Options -> Advanced (Tab)
--and set Maximum characters per column
--to a high number, such as 10000, so
--that all the code will be displayed.
--http://www.gringod.com/2007/02/27/scripting-permissions-in-sql-server/
DECLARE  @DatabaseRoleName [SYSNAME]
--SET @DatabaseRoleName = '{Database Role Name}'
SET @DatabaseRoleName = 'DataEntryOperator'
SET NoCount  ON
DECLARE  @errStatement      [VARCHAR](8000),
         @msgStatement      [VARCHAR](8000),
         @DatabaseRoleID    [SMALLINT],
         @IsApplicationRole [BIT],
         @ObjectID          [INT],
         @ObjectName        [SYSNAME]
SELECT @DatabaseRoleID = [uId],
       @IsApplicationRole = CAST([IsapProle] AS BIT)
FROM   [dbo].[sysUsers]
WHERE  [Name] = @DatabaseRoleName
       AND ([IssqlRole] = 1
             OR [IsapProle] = 1)
       AND [Name] NOT IN ('public',
                          'INFORMATION_SCHEMA',
                          'db_owner',
                          'db_accessadmin',
                          'db_securityadmin',
                          'db_ddladmin',
                          'db_backupoperator',
                          'db_datareader',
                          'db_datawriter',
                          'db_denydatareader',
                          'db_denydatawriter')
IF @DatabaseRoleID IS NULL
  BEGIN
    IF @DatabaseRoleName IN ('public',
                             'INFORMATION_SCHEMA',
                             'db_owner',
                             'db_accessadmin',
                             'db_securityadmin',
                             'db_ddladmin',
                             'db_backupoperator',
                             'db_datareader',
                             'db_datawriter',
                             'db_denydatareader',
                             'db_denydatawriter')
      SET @errStatement = 'Role ' + @DatabaseRoleName + ' is a fixed database role and cannot be scripted.'
    ELSE
      SET @errStatement = 'Role ' + @DatabaseRoleName + ' does not exist in ' + Db_name() + '.' + CHAR(13) + 'Please provide the name of a current role in ' + Db_name() + ' you wish to script.'
    RAISERROR (@errStatement,16,1)
  END
ELSE
  BEGIN
    SET @msgStatement = '--Security creation script for role ' + @DatabaseRoleName + CHAR(13) + '--Created At: ' + CONVERT(VARCHAR,Getdate(),112) + REPLACE(CONVERT(VARCHAR,Getdate(),108),':','') + CHAR(13) + '--Created By: ' + Suser_name() + CHAR(13) + '--Add Role To Database' + CHAR(13)
    IF @IsApplicationRole = 1
      SET @msgStatement = @msgStatement + 'EXEC sp_addapprole' + CHAR(13) + CHAR(9) + '@rolename = ''' + @DatabaseRoleName + '''' + CHAR(13) + CHAR(9) + '@password = ''{Please provide the password here}''' + CHAR(13)
    ELSE
      BEGIN
        SET @msgStatement = @msgStatement + 'EXEC sp_addrole' + CHAR(13) + CHAR(9) + '@rolename ''' + @DatabaseRoleName + '''' + CHAR(13)
        PRINT 'GO'
      END
    SET @msgStatement = @msgStatement + '--Set Object Specific Permissions For Role'
    PRINT @msgStatement
    DECLARE _sySobjects CURSOR LOCAL FORWARD_ONLY READ_ONLY FOR
    SELECT DISTINCT ([sySobjects].[Id]),
                    '[' + User_name([sySobjects].[uId]) + '].[' + [sySobjects].[Name] + ']'
    FROM   [dbo].[sysProtects]
           INNER JOIN [dbo].[sySobjects]
             ON [sysProtects].[Id] = [sySobjects].[Id]
    WHERE  [sysProtects].[uId] = @DatabaseRoleID
    OPEN _sySobjects
    FETCH NEXT FROM _sySobjects
    INTO @ObjectID,
         @ObjectName
    WHILE @@FETCH_STATUS = 0
      BEGIN
        SET @msgStatement = ''
        IF EXISTS (SELECT *
                   FROM   [dbo].[sysProtects]
                   WHERE  [Id] = @ObjectID
                          AND [uId] = @DatabaseRoleID
                          AND [Action] = 193
                          AND [ProtectType] = 205)
          SET @msgStatement = @msgStatement + 'SELECT,'
        IF EXISTS (SELECT *
                   FROM   [dbo].[sysProtects]
                   WHERE  [Id] = @ObjectID
                          AND [uId] = @DatabaseRoleID
                          AND [Action] = 195
                          AND [ProtectType] = 205)
          SET @msgStatement = @msgStatement + 'INSERT,'
        IF EXISTS (SELECT *
                   FROM   [dbo].[sysProtects]
                   WHERE  [Id] = @ObjectID
                          AND [uId] = @DatabaseRoleID
                          AND [Action] = 197
                          AND [ProtectType] = 205)
          SET @msgStatement = @msgStatement + 'UPDATE,'
        IF EXISTS (SELECT *
                   FROM   [dbo].[sysProtects]
                   WHERE  [Id] = @ObjectID
                          AND [uId] = @DatabaseRoleID
                          AND [Action] = 196
                          AND [ProtectType] = 205)
          SET @msgStatement = @msgStatement + 'DELETE,'
        IF EXISTS (SELECT *
                   FROM   [dbo].[sysProtects]
                   WHERE  [Id] = @ObjectID
                          AND [uId] = @DatabaseRoleID
                          AND [Action] = 224
                          AND [ProtectType] = 205)
          SET @msgStatement = @msgStatement + 'EXECUTE,'
        IF EXISTS (SELECT *
                   FROM   [dbo].[sysProtects]
                   WHERE  [Id] = @ObjectID
                          AND [uId] = @DatabaseRoleID
                          AND [Action] = 26
                          AND [ProtectType] = 205)
          SET @msgStatement = @msgStatement + 'REFERENCES,'
        IF len(@msgStatement) > 0
          BEGIN
            IF RIGHT(@msgStatement,1) = ','
              SET @msgStatement = LEFT(@msgStatement,Len(@msgStatement) - 1)
            SET @msgStatement = 'GRANT' + CHAR(13) + CHAR(9) + @msgStatement + CHAR(13) + CHAR(9) + 'ON ' + @ObjectName + CHAR(13) + CHAR(9) + 'TO ' + @DatabaseRoleName
            PRINT @msgStatement
          END
        SET @msgStatement = ''
        IF EXISTS (SELECT *
                   FROM   [dbo].[sysProtects]
                   WHERE  [Id] = @ObjectID
                          AND [uId] = @DatabaseRoleID
                          AND [Action] = 193
                          AND [ProtectType] = 206)
          SET @msgStatement = @msgStatement + 'SELECT,'
        IF EXISTS (SELECT *
                   FROM   [dbo].[sysProtects]
                   WHERE  [Id] = @ObjectID
                          AND [uId] = @DatabaseRoleID
                          AND [Action] = 195
                          AND [ProtectType] = 206)
          SET @msgStatement = @msgStatement + 'INSERT,'
        IF EXISTS (SELECT *
                   FROM   [dbo].[sysProtects]
                   WHERE  [Id] = @ObjectID
                          AND [uId] = @DatabaseRoleID
                          AND [Action] = 197
                          AND [ProtectType] = 206)
          SET @msgStatement = @msgStatement + 'UPDATE,'
        IF EXISTS (SELECT *
                   FROM   [dbo].[sysProtects]
                   WHERE  [Id] = @ObjectID
                          AND [uId] = @DatabaseRoleID
                          AND [Action] = 196
                          AND [ProtectType] = 206)
          SET @msgStatement = @msgStatement + 'DELETE,'
        IF EXISTS (SELECT *
                   FROM   [dbo].[sysProtects]
                   WHERE  [Id] = @ObjectID
                          AND [uId] = @DatabaseRoleID
                          AND [Action] = 224
                          AND [ProtectType] = 206)
          SET @msgStatement = @msgStatement + 'EXECUTE,'
        IF EXISTS (SELECT *
                   FROM   [dbo].[sysProtects]
                   WHERE  [Id] = @ObjectID
                          AND [uId] = @DatabaseRoleID
                          AND [Action] = 26
                          AND [ProtectType] = 206)
          SET @msgStatement = @msgStatement + 'REFERENCES,'
        IF len(@msgStatement) > 0
          BEGIN
            IF RIGHT(@msgStatement,1) = ','
              SET @msgStatement = LEFT(@msgStatement,Len(@msgStatement) - 1)
            SET @msgStatement = 'DENY' + CHAR(13) + CHAR(9) + @msgStatement + CHAR(13) + CHAR(9) + 'ON ' + @ObjectName + CHAR(13) + CHAR(9) + 'TO ' + @DatabaseRoleName
            PRINT @msgStatement
          END
        FETCH NEXT FROM _sySobjects
        INTO @ObjectID,
             @ObjectName
      END
    CLOSE _sySobjects
    DEALLOCATE _sySobjects
    PRINT 'GO'
  END

iterating Through controls

FROM:

Chris Pietschmann

Yet to try this out!!!
http://pietschsoft.com/post/2006/06/01/Javascript-Loop-through-all-elements-in-a-form.aspx


<html>
<head>
<script type="text/javascript">
    function DisplayFormValues()
    {
        var str = '';
        var elem = document.getElementById('frmMain').elements;
        for(var i = 0; i < elem.length; i++)
        {
            str += "<b>Type:</b>" + elem[i].type + "&nbsp&nbsp";
            str += "<b>Name:</b>" + elem[i].name + "&nbsp;&nbsp;";
            str += "<b>Value:</b><i>" + elem[i].value + "</i>&nbsp;&nbsp;";
            str += "<BR>";
        }
        document.getElementById('lblValues').innerHTML = str;
    }
</script>
</head>
<body>
    <form id="frmMain" name="frmMain">
        <input type="hidden" name="ElemHidden" value="some hidden text" />
        <input type="text" name="ElemText" value="some text" /><br />
        <textarea name="ElemTextArea">Some text area text</textarea><br />
        <br />
        <input type="button" value="Test" onclick="DisplayFormValues();" />
    </form>
    <hr />
    <div id="lblValues"></div>
</body>
</html>