Archive for February, 2007

Feb 27 2007

Scripting Permissions in SQL Server

Published by under Programming

I shalln’t tell you my exact reasons for discovering this information but believe me when I say it took about an hour of clicking several thousand checkboxes and thinking “there must be a better way of doing this”.

What I was doing was setting permissions on an MS SQL Server database that didn’t have roles set up in it. What I needed was a way to export roles and their permissions from one database and insert them into another database. The “Script Role” feature in SQL Server Management Studio on created a role, it didn’t script the effective permissions of the role.

Luckily I found the How to Script User and Role Object Permissions in SQL Server page on Sql-Server-Performance.com. That provides sql scripts that interrogates an SQL Server database and role/user and generates a script that can be used to set up that role/user in another database.

Obviously it goes without saying that you should be using roles in databases and assigning users to roles at the server level, not the database level, as when start moving databases around the roles will be moved but the users may not exists on the next server. Also, wherever possibly you should be using Windows Authentication and not SQL Server Authentication and you should not be using the SA account account.

Just in case the above site goes down I’ll include the script for export role permissions below:

--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.

DECLARE  @DatabaseRoleName [SYSNAME]
--SET @DatabaseRoleName = '{Database Role Name}'

SET @DatabaseRoleName = 'role_name_goes_here'

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

3 responses so far

Feb 27 2007

Using MAMP on a Mac

Published by under PHP,Programming

I’ve had MAMP installed on my Mac since the day I got it. Yeah, yeah, laugh if you want but I find *AMP a fast platform for developing on and have used since my first year at Uni.

Anyhow, installing MAMP and getting it running was incredibly easy. In fact the installer did everything (so long as you use the default directories). For this reason I’m not going to be covering the basic installation. What I am going to cover is getting the MAMP installation setup as the default installation of PHP as used on the command line. I’ll also cover getting PEAR working on the command line and even as far as setting up Trax for use on the command line. For the article I’m going to assume you’re using the MAMP default settings with PHP5.

Once you have installed MAMP on your Mac you can use it like you would on a webserver, you just need to point you browser at http://localhost:8080. This is all good until you need to use PHP on the command line. If you type the following on the command line:

php -v

You’ll probably find that the output indicates that the command line version of PHP is 4.x and not PHP 5.x like you want it to be. The fix for this is quite simple, you just need to run the follow commands on the command line:

sudo mv /usr/bin/php /usr/bin/php-old
sudo ln -s /Applications/MAMP/bin/php5/bin/php /usr/bin/php

This will make the MAMP installed version of PHP the default on the command line. If you want to do the same for the MAMP installed version of PEAR then you need to do the following:

sudo mv /usr/bin/pear /usr/bin/pear-old
sudo ln -s /Applications/MAMP/bin/php5/bin/pear /usr/bin/pear

Now you can go ahead and use PHP and PEAR on the command line like all the installation guides and tutorials show you.

FInally, if you are installing PHPonTrax here are a few things that will make your life easier. The documents tell you to use the trax command. If you try this, however, you’ll probably get a message that the application trax cannot be found. To make trax available run the following command:

sudo ln -s /Applications/MAMP/bin/php5/bin/trax /usr/bin/trax
Also, when the trax documentation tells you to run php scripts on the command line always prepend it with php for example:

./generate.php model

becomes:

php ./generate.php model

I hope this help alleviate some of your problems. If not, pour yourself a coffee, head over to http://www.askaninja.com/ and watch a couple of episodes, then drop me a comment asking for help. I may not know all the answers but I should be able to point you in the right direction.

Technorati Tags: , , , , ,

9 responses so far

Feb 13 2007

Flashing A SE K750i

Published by under Technology

I have finally been able to upgrade the software on my K750i. The reason I need to upgrade was that over the weekend I wrote my first Java application for mobiles and I wanted to test it on my phone, the only problem being that the USB transfer of the old photo software was very buggy.

Try as I might I could not flash the phone over the weekend from inside Parallels on my MacBook Pro. I think that some information just wasn’t making it through the Parallels layer to get to the Update Service software. I figured this was a problem as I could do it when I got to work on Monday.

Nope, I was wrong. It seems that despite following all the instruction to the letter, sending a support request to Sony Ericsson and following the instructions in the response I was still unable to got the software to work. It kept giving me an error about require software versions not found. In the end I did a little digging around and found, in a configuration file, a reference to Emma III, which appears to be the SE and partners update service. If you try and download the Emma Install Pack you wont be able to do much with it as it requires a username and password to run it. I did however download the Emma System Analyse software which (by pure luck) I was able to run against the Update Service software to confirm that the update service had everything it required.

Finally I want back to the Update Service directory and tried running every application that was in there. The last program I tried was ma3platform.exe and this is where I struck gold! Immediately it started complaining about msstyle and Anthracite (the desktop theme I was running). So it seemed that it was the windows theme that was causing all the problems.

A quick change to the desktop theme to set it back to the default XP theme and I was up and running. The Update Service ran like a dream and I now have the latest software version on my phone.

If you want to check what version software you have on your Sony Ericsson phone you need to do the following from the standby screen (no menus or applications open):

right, *, left, left, *, left, *.
Go to service info, software info, the first line of the info is the firmware ver.

One thing that did intrigue me about the Update Service is that ma3platform.exe seems to be the same as the Emma III software and requires a username and password, this must be stored in the Update Service software on the local machine.  Would this same username and password work in the Emma II software?  Does the Emma III software provide any more functionality to the Update Service software?  I guess there's only one way to find out ;)

2 responses so far

Feb 10 2007

Broken Downloads Now Fixed

Published by under Site Work

I’d like to apologise to everyone that has tried to download files from this site recent and ended up getting a corrupted file. This has now been fixed.

I had no idea it was broken until a couple of people comment on one of my posts. It could have been broken just a day, or possibly much long – even as long ago as when I switched over to PHP5.

If you have downloaded a corrupt file please try again, and if it still isn’t working please let me know… I can test everything on the site and so rely on visitors to let me know when they find something that is broken.

No responses yet

Feb 03 2007

Megalithic Guernsey

Published by under General

Here is another layer for use in Google Earth. This one is a mapping of all the megalithic sites identified in Andrew Fothergills book Megalithic Guernsey. The book is only available from bookshops in Guernsey or directly from the Megalithic Guernsey website (http://www.megalithicguernsey.co.uk).

The layer contains a description of each site, taken from the above website, and a link from each site to the appropriate page on the books website that contains further information and pictures.

I am hoping, now that I got a nice shiny new Canon EOS 30D, to get out and take some pictures of these sites myself.

Download the Megalithic Guernsey layer.

Creative Commons License
This work is licensed under a Creative Commons Attribution-Noncommercial-Share Alike 2.5 License.

No responses yet