At 11:35 AM 30/12/2008, you wrote:

>Hi Alan and Dean;I thought the problem would be more obvious but it isn't. I created two databases on the same server on another computer and can create roles with the same names and drop them at leisure.
>The problem computer is as described. I have two databases on the same server. I can create and drop roles on one database (no grants - just CREATE ROLE MY_ROLE) but cannot use the same role name on the second database. I thought it was a server problem.I will post the error messages tomorrow when I get back to my other computer.

A role on its own doesn't do anything. It is nothing but a container for a package of permissions on the database objects that you want the role to be able to access.

Therefore, after creating the ROLE, you then have to grant permissions on your **database objects** to that role. The intelligent way to go about this is to sit down with your database documentation, tabulate the required permissions on paper, and then write an isql script for the GRANTS.

Only after that, you can start granting that role to individual users.

If you didn't do the right thing before (you don't have a script for the old DB that you can edit to suit the new one) there are various tools around that you can use to extract a script of the permissions in the old DB. For example, the Metadata Extract tool in IB_SQL lets you check off all object types except "Permission" and will output a script that you can edit to your heart's content.

./hb


If you have a copy of a database where you already have