RE:Role names and user ids

Sounds as if you did not revoke connection authority from the user "VIEWONLY". execute the following SQL: select * from sysusers where username="VIEWONLY";if the usertype is - D,R,C you need to revoke access from user "VIEWONLY" until no rows are returned for the above query. When you are able to add the ROLE "VIEWONLY" the usertype will be "G" in the sysusers table. Hope this helps! C.L. Kendricks --------------------------( Forwarded letter 1 follows )--------------------- Date: Monday, 21 April 1997 6:15pm From: jnelaine@erols.com Subject: Role names and user ids Date:Mon, 21 Apr 1997 17:06:06 -0500 Reply-To: "Jay D. Sullivan" <jnelaine@erols.com> Organization: Erol's Internet Services Sender: informix-list-owner@rmy.emory.edu To: informix-list@rmy.emory.edu X-Informix-List-Id: <news.36959> Hello, We have Online Workgroup Server 7.12.TC3 running on a Windows NT 4.0 server. I am trying to set up a few new roles with the "CREATE ROLE" statement and I'm having a problem creating a particular role. I would like to create a role called VIEWONLY. However, I didn't realize that you can't have a role with the same name as a login id. I had already created an NT user with the login id VIEWONLY. I had also logged into Informix at least once as VIEWONLY. After I realized that I couldn't have a role with the same name as a login id, I deleted the VIEWONLY user from NT. However, Informix must still think that it exists because I still get the same error when I try to add it. The error messages is: "SQL Error (-19800): Role name already exists as a user or role." I could not find this error in the Find Error utility. I ran a few tests and it seems like Informix is storing the login ids of everyone who has logged in and is checking that list for conflicts when setting up a role. For example, I can create an NT user called "ABC" and still create a role called "ABC". It is not until I have actually logged into Informix as ABC when I start having a problem. After I have logged in as ABC and then logged out and killed the NT user ABC, I cannot create a role called ABC. Informix still thinks that the user exists. Does Informix store these login ids in some system table where I can delete the non-existant VIEWONLY user? Just to let you know, I would normally give up and choose a different name besides VIEWONLY, but that is not practical in this situation. Thanks for any help, Jay Sullivan.