190
Few days back my IT Department was forced to format the database server due to some technical problem, after formatting when we restored data, found that our users are not accessible. Our partner suggested to delete all existing users and create new. Now all users have lost their customized homepages and reports as well. Is there any way to solve this problem?
*This post is locked for comments
Yes all my users are available in SQL but still not able to ligin.
As per our previous practice, after restoring the database user deletion was required in both gp and in spl and then new users can be created. in gp.
Regards
Malik Saleem
Pakistan
Open SQL Server and check if the users created properly.
Dear Daoud, After executing above script following error is coming,
"Msg 15287, Level 16, State 1, Procedure sp_change_users_login, Line 35
Terminating this procedure. 'dbo' is a forbidden value for the login name parameter in this procedure."
after running this script, I changed the password for one use and try to login, following message came,
"A get/change first operation on table SY_USER_MSTR" failed accessing SQL data"
Again thanks for your cooperation.
Regards,
Malik Saleem
Pakistan
It is working with your same environment, however please try the script below:
DECLARE @USER varchar(500)
DECLARE @SID varchar(500)
DECLARE CURR CURSOR FOR
SELECT NAME, SID FROM sysusers WHERE issqluser = 1 and (sid is not null and sid <> 0x0)
and suser_sname(sid) is null ORDER BY name
OPEN CURR
FETCH NEXT FROM CURR INTO @USER, @SID
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC sp_change_users_login 'Auto_Fix', @USER, NULL , @SID
FETCH NEXT FROM CURR INTO @USER, @SID
END
CLOSE CURR
DEALLOCATE CURR
We are using SQL 2005 with SP2.
Thanks again
Malik Saleem
What is your SQL version?
Dear Daoud , after running the script following errors come,
Msg 15600, Level 15, State 1, Procedure sp_change_users_login, Line 174
An invalid parameter or option was specified for procedure 'sys.sp_change_users_login'.
Msg 15600, Level 15, State 1, Procedure sp_change_users_login, Line 174
An invalid parameter or option was specified for procedure 'sys.sp_change_users_login'.
Msg 15600, Level 15, State 1, Procedure sp_change_users_login, Line 174
An invalid parameter or option was specified for procedure 'sys.sp_change_users_login'.
Msg 15600, Level 15, State 1, Procedure sp_change_users_login, Line 174
An invalid parameter or option was specified for procedure 'sys.sp_change_users_login'.
Msg 15600, Level 15, State 1, Procedure sp_change_users_login, Line 174
An invalid parameter or option was specified for procedure 'sys.sp_change_users_login'.
Msg 15600, Level 15, State 1, Procedure sp_change_users_login, Line 174
An invalid parameter or option was specified for procedure 'sys.sp_change_users_login'.
Msg 15600, Level 15, State 1, Procedure sp_change_users_login, Line 174
An invalid parameter or option was specified for procedure 'sys.sp_change_users_login'.
Msg 15287, Level 16, State 1, Procedure sp_change_users_login, Line 35
Terminating this procedure. 'dbo' is a forbidden value for the login name parameter in this procedure.
Msg 15600, Level 15, State 1, Procedure sp_change_users_login, Line 174
An invalid parameter or option was specified for procedure 'sys.sp_change_users_login'.
Msg 15600, Level 15, State 1, Procedure sp_change_users_login, Line 174
An invalid parameter or option was specified for procedure 'sys.sp_change_users_login'.
Msg 15600, Level 15, State 1, Procedure sp_change_users_login, Line 174
An invalid parameter or option was specified for procedure 'sys.sp_change_users_login'.
Msg 15600, Level 15, State 1, Procedure sp_change_users_login, Line 174
An invalid parameter or option was specified for procedure 'sys.sp_change_users_login'.
Msg 15600, Level 15, State 1, Procedure sp_change_users_login, Line 174
An invalid parameter or option was specified for procedure 'sys.sp_change_users_login'.
Msg 15600, Level 15, State 1, Procedure sp_change_users_login, Line 174
An invalid parameter or option was specified for procedure 'sys.sp_change_users_login'.
Msg 15600, Level 15, State 1, Procedure sp_change_users_login, Line 174
An invalid parameter or option was specified for procedure 'sys.sp_change_users_login'.
Msg 15600, Level 15, State 1, Procedure sp_change_users_login, Line 174
An invalid parameter or option was specified for procedure 'sys.sp_change_users_login'.
Msg 15600, Level 15, State 1, Procedure sp_change_users_login, Line 174
An invalid parameter or option was specified for procedure 'sys.sp_change_users_login'.
Msg 15600, Level 15, State 1, Procedure sp_change_users_login, Line 174
An invalid parameter or option was specified for procedure 'sys.sp_change_users_login'.
Msg 15600, Level 15, State 1, Procedure sp_change_users_login, Line 174
An invalid parameter or option was specified for procedure 'sys.sp_change_users_login'.
Msg 15600, Level 15, State 1, Procedure sp_change_users_login, Line 174
An invalid parameter or option was specified for procedure 'sys.sp_change_users_login'.
Msg 15600, Level 15, State 1, Procedure sp_change_users_login, Line 174
An invalid parameter or option was specified for procedure 'sys.sp_change_users_login'.
Msg 15600, Level 15, State 1, Procedure sp_change_users_login, Line 174
An invalid parameter or option was specified for procedure 'sys.sp_change_users_login'.
Msg 15600, Level 15, State 1, Procedure sp_change_users_login, Line 174
An invalid parameter or option was specified for procedure 'sys.sp_change_users_login'.
Msg 15600, Level 15, State 1, Procedure sp_change_users_login, Line 174
An invalid parameter or option was specified for procedure 'sys.sp_change_users_login'.
Dear Daoud, Thanks for giving me this great help.
I posted an article about this subject, locate the script in the article below:
mohdaoud.blogspot.com/.../fix-sql-orphaned-users-create-sql-users.html
#1
André Arnaud de Cal...
291,711
Super User 2024 Season 2
#2
Martin Dráb
230,466
Most Valuable Professional
#3
nmaenpaa
101,156
Share
Report
All responses (
Answers (
