More: Server user xxx not a valid user in database yyy
-
I have a SQL2000 SP3 server with 30+ logins (1 login per db). Whenever one is using EM or QA and "Right-Click" Script to new window as... The error "[Microsoft][ODBC SQL Server Driver][SQL Server]Server user xxx is not a valid user in database yyy" pops up. The user xxx is often different but the database yyy is always the same one (never the one that the user is defaulted to). The irony is that you can dismiss the error and do the same action again and it works.
The latest issue we encountered is using SQLDMO. The .Connect() method always fails with the above error message.
I have run sp_change_users_login 'Report' but it comes up empty. I have eyeballed each db to make sure that the associated user is granted dbo with the proper perms.
Most of the db's were migrated from an older machine to this one using the backup from old machine->create empty db and user on new machine->then restore. The sp_change_users_login was run after this to fix the SID's of course.
Where can I go from here? Any help pointing me towards a resolution would be awesome.
THX
-Ben
-
Here is my though. This seems to be a lot of work but since you only have 30 logins it shouldn't be that big of a deal.
How about sp_remokeddbaccess on all logins in all of your databases.
add the user back to your databases. You should no longer has any more of that problem.
mom
-
Add the following proc to the master db, execute it using the information in the proc and then remove it. See if it resolves the problem.
CREATE PROCEDURE SP_AUTOFIX_USERS
AS
/* USAGE FOR FIX USER SIDS FOR ALL DATABASES
SP_MSFOREACHDB "USE ?; EXEC SP_AUTOFIX_USERS;"
*/
-- Declare the variables to store the values returned by FETCH.
set nocount on
declare @login sysname
PRINT DB_NAME()
PRINT '--------'
DECLARE user_update_cursor CURSOR FOR
SELECT distinct name from sysusers where issqluser = 1 and name not in ('dbo', 'guest') order by name
OPEN user_update_cursor
-- Perform the first fetch and store the values in variables.
-- Note: The variables are in the same order as the columns
-- in the SELECT statement.
FETCH NEXT FROM user_update_cursor
INTO @login
-- Check @@FETCH_STATUS to see if there are any more rows to fetch.
WHILE @@FETCH_STATUS = 0
BEGIN
print @login
exec sp_change_users_login 'update_one', @login, @login
-- This is executed as long as the previous fetch succeeds.
FETCH NEXT FROM user_update_cursor
INTO @login
END
CLOSE user_update_cursor
DEALLOCATE user_update_cursor
-
Hello All,
I have written a add /Update trigger on a table of XXX database which will copy changes made on this table to a table in YYY database. If am running application which uses xxx database with sa user all the things going OK
But if i changes login "[Microsoft][ODBC SQL Server Driver][SQL Server]Server user AAA is not a valid user in database yyy"
Anybody please help me....................................................
regards
sam
-
You would need to make sure that the login you are using has access to the database and table that you would be updating when the trigger fires.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply