problem granting DB access rights of Sql Server Login

[pipemole]

To tek-tips forums, Home > Forums > Programmers > DBMS Packages
Microsoft SQL Server: Setup and Administration Forum
dt; sun.22aug.2004, evening;
Sbj: Sql Server Login creation for use with IAS, problem granting DB access rights;
Hello:
In Enterprise Manager for MS SQL-Server7, I am attempting to set up an SQL Server login, employing Sql Server authentication, which should be given the ability to access one of the data bases, ‘Inventory’ which I’ve created. (I also let Master be its default db, and try to set role and user settings in ‘Inventory’ db-> Roles or Users, or in Security ->Users.)

The user name comes from documents suggesting which login to employ in conjunction with Internet Information Service 4.0 web server, in an application to place info generated by database queries onto web pages. I’ve removed and recreated the database login and odbc datasource definition a few times already, with slight variations on creating / editing the login, but with the same error continuing to arise. Hence the web application continues to be unable to display the pages containing query results. The web & db app have been previously running on other machines, but setting of access rights seems to not be working out on this system. Operating system is NT-Server4.

The error message shown is
Microsoft Sql-Dmo Odbc Sql State 42000; error 15023, user or role IUSR_<ServerName> already exists in the current database;

How can this problem be overcome?

Signed, - - pipemole - -

 

[nigelrivett]

It's just saying that the user already exists and so cannot be created. This could be because you have an orphan user with that name.

Using query analyser
In the database try
select * from sysusers where name = 'username'
If the user exists then drop it using sp_dropuser

select * from master..syslogins where name = 'loginname'
check the sid and make sure it is not mapped to a user in sysusers - if it is then drop that user using sp_dropuser (probably not or you would see it from enterprise manager).

Now you should be able to add the user to the database.


This sort of thing commonly happens when you restore a database from another server.

======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.