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>

Disabling the Back button in browser

Found at:
http://viralpatel.net/blogs/2009/11/disable-back-button-browser-javascript.html

Seems to work in IE need to try in others
BUT just in case:

Following is the code to open webpage in a new window have no toolbar (Back/Next buttons).

1 2	window.open ("http://viralpatel.net/blogs/", "mywindow","status=1,toolbar=0");

Also it is possible to disable the right click on any webpage using Javascript. Add following code in the webpage.

?

1	<body oncontextmenu="return false;">

Disable Back functionality using history.forward

This is another technique to disable the back functionality in any webpage. We can disable the back navigation by adding following code in the webpage. Now the catch here is that you have to add this code in all the pages where you want to avoid user to get back from previous page. For example user follows the navigation page1 -> page2. And you want to stop user from page2 to go back to page1. In this case all following code in page1.

1 2 3 4 5 6 7

<SCRIPT type="text/javascript">     window.history.forward();     function noBack() { window.history.forward(); } </SCRIPT> </HEAD> <BODY onload="noBack();"     onpageshow="if (event.persisted) noBack();" onunload="">

Deprecated iframe in HTML5

Found this useful site: http://www.dreamincode.net/forums/topic/195687-iframe-replacement/
uses the object tag instead of iframe.
There is no REAL replacement - DIVs may be a way to section off a bit of a page
but still NOT a replacement

In essence:

Adding Functionality
Go back to your "index.html" file, and change your object element to look like this:

We have just added some functionality to the object element. Save it and open it up in your browser. "framed.html" will show up in a small part of the page!

Finishing
However, we aren't finished just yet! We still have to add the backup in case a URL that doesn't work is placed into the object element. We will call this code the "Fallback code" that the browser goes to when a link doesn't work.
Add this to your object element:

<object data="framed.html" type="text/html"><p>This is the fallback code!</p></object>

SQL Server 2005 installation issue mscorlib.tlb

source:
http://support.microsoft.com/kb/918685

issue mscorlib.tlb could not be loaded;

Fix as per above (incase it is moved)

For SQL Server 2005

To work around this problem, you must manually specify the correct path of the Mscorlib.tlb file in the registry. To do this, follow these steps:

1. Click Start, click Run, type regedit, and then click OK.
2. Locate the following registry subkey:
   HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\90\Tools\Shell\TlbAutoRepair\mscorlib.tlb
   Notes
   • If you install SQL Server 2005 on 64-bit operating systems, locate the following registry subkey:
     HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\Microsoft SQL Server\90\Tools\Shell\TlbAutoRepair\mscorlib.tlb
   • If you install SQL Server 2005 Express Edition with Advanced Services, locate the following registry subkey:
     HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\90\Tools\ShellSEM\TlbAutoRepair\mscorlib.tlb
3. In the right pane, double-click the registry entry TlbPath. The Edit String dialog box appears.
4. In the Value data box, replace %CLRVERSION% with the following value:
   v2.0.50727
   Note The value v2.0.50727 indicates the version of the .NET Framework that the original release version of SQL Server 2005 uses.
5. Click OK, and then exit Registry Editor.

Important If you install a SQL Server 2005 service pack or hotfix, the service pack or hotfix will change the value that you replace in step 4 back to %CLRVERSION%. If the problem that is described in this article still exists after you install the service pack or the hotfix, you must following these steps again to work around this problem.

Issue Printing to shared printer under asp.net crystal reports

Found by a colleague but really useful, .net application with crystal reports stopped printing with a new version of .net

http://support.microsoft.com/kb/184291/en-us

Saving an image in a SQL Server DB

Useful link:  this link proved really useful
http://www.dotnet-guide.com/save-image-in-sql.html