SQL 7 Security Permission problem with attached DB user

[lsgko]

After installing windows 2000 fresh (NT got corrupted). I installed SQL Server 7 and was able to attach the databases from before NT got corrupt.

I was able to create the user used by our ODBC program, but when I try to access it I get the following error message:

From the program:
[Microsoft] [ODBC SQL Server Driver] [SQL Server] Cannot open database request in login DB. Login fails.

From Query Analyzer:
Server: Msg 916, Level 14, State 1
Server User X is not a valid user in database DB

I then used sp_dropuser to remove the user from the DB and then added the user back (I don't know if this corrupts data or anything, I've got a backup if I need it) and was able to select the database in query analyzer but now I don't have permission to run a select (or likely any other command).

I tried a grant all to X as sa but that didn't work.

Any ideas?

 

[nigelrivett]

The user has a login and login_id. This maps to a user and user_id in the database. When you attached the database the database still had the user but it did not map to the correct login_id which is why the user coiuld not access the database. Dropping and adding the user corrects the link and allows access again. It doesn't corrupt anything and is the correct thing to do.

Not sure what you mean by granting all to x as sa.
You can add the login to the sysadmin server role which will allow the user to do anything on the server - doesn't need the database user to be added.
You could add the database user to the database owner role which allows them to do anything in that database.
The other option is to grant execute on the stored procs and select, insert, update on tables/views.

For testing permissions it's always worth using query analyser first.

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