mrroofer


Please help, IĦŻve been trying too long to figure this out. I restored a backup database in SQL express, and I cannot log into that database. Invalid user name or password every time. Does any body have a clue on what IĦŻm doing wrong.

Please Help




Re: Cannot log into database

Madhu K Nair


r u trying to connect to the database through your application if yes... then Check the Connection string and check the login id and password used in the connection string.

Madhu







Re: Cannot log into database

ALZDBA

are you using sql-authentication

If yes, you may need to sync your sqlexpress user-id's with your db-userid's.

use this script to generate the code:


use yourdb -- User-db
go
print 'print @@servername + '' / '' + db_name()'
print 'go'
go

declare @username varchar(128)
declare @Musername varchar(128)
declare @UserType Char(1)
declare @sql_stmt varchar(500)
declare @ExcludeWindowsAccounts Char(1)
set @ExcludeWindowsAccounts = 'N' -- adjust if you want to exclude windows accounts!

--cursor returns with names of each username to be tied to its respective
DECLARE user_cursor CURSOR FOR
SELECT su.name as Name, msu.name as MasterName , su.type as UserType
FROM sys.database_principals su
left join sys.sql_logins msu
on upper(su.name) = upper(msu.name)
where su.type in ('S', 'U', 'G')
-- WHERE su.sid > 0x00
ORDER BY Name

--for each user:
OPEN user_cursor
FETCH NEXT FROM user_cursor INTO @username, @Musername, @UserType
WHILE @@FETCH_STATUS = 0
BEGIN
IF @username NOT IN ('dbo', 'guest', 'sys', 'INFORMATION_SCHEMA', 'list of names you want to avoid')

BEGIN
if @Musername is null
begin
if @UserType in ('U','G')
begin
if @ExcludeWindowsAccounts = 'N'
begin
print 'if not exists (select * from master.dbo.syslogins where loginname = N''NtDomein**\' + @username + ''')'
print ' begin '
print ' exec sp_grantlogin N''NtDomein**\' + @username + ''''
print ' exec sp_defaultdb N''NtDomein**\' + + @username + ''', N'''+ db_name() + ''''
print ' end'
set @sql_stmt = '--Windows account gehad'
end
else
begin
set @sql_stmt = '--'
end

end
else
begin
SELECT @sql_stmt = 'sp_change_users_login @Action = ''Auto_Fix'',@UserNamePattern = ''' + @username + ''', @LoginName = NULL, @Password = -- provide password'
end
end
else
begin
SELECT @sql_stmt = 'sp_change_users_login @Action = ''Update_One'',@UserNamePattern = ''' + @username + ''', @LoginName = ''' + @username + ''''
end

PRINT @sql_stmt
print 'go'
print '--*** Notice: exec stmt commented !!! ***'
--EXECUTE (@sql_stmt)
END
FETCH NEXT FROM user_cursor INTO @username, @Musername, @UserType
END --of table-cursor loop

--clean up
CLOSE user_cursor
DEALLOCATE user_cursor

Print '** the end : User-synchronisation **'







Re: Cannot log into database

Ekrem Önsoy

I think your situation could be like this:

After backing up your database, you created another user object in that database and started using that user to reach to that database after that.

However, after a while you decided restoring your database and as you created that user object after backing up your database, there was no such user object in your backup and so after you restored your database, that user was not there. But you still are trying to use that user object which is not in Security node in your restored database.

This is just one of common scenarios... May not apply you or may, we will see in your response.






Re: Cannot log into database

Vidhya Sagar

Hi,

Have you fixed mismatch ID's after restoration If not there there will be difference in SID of database user and in sql login, so correct mismatch ID and then try taking backup.

Login with SA privilege \ secyrity admin & run the below query to find any mismatch
For SQL 2000

Code Block

select name, SID from sysusers where name <> 'guest' and name <> 'dbo' and sid not in (select sid from master..syslogins)


For SQL 2005
Code Block

select name,sid from sys.database_principals where sid not in (select sid from sys.server_principals) and principal_id > 4 and type <> 'R'


If you have any mismatch then use the below query to correct it
Code Block

Exec sp_change_users_login @Action = ''Update_One'',@UserNamePattern = username , @LoginName = username.

Where username is the mismatch user name





Re: Cannot log into database

mrroofer

Yes, you are right. But I don't know how to fix this.

Please help

Thank you so much





Re: Cannot log into database

mrroofer

Yes your exactly right, please tell me how to fix this. Sorry took so long to resond, I just seemed to give up. But youre on the right track.

Please help





Re: Cannot log into database

Ekrem Önsoy

Are you talking to me mrroofer Because the thread seems answered however you still keep asking






Re: Cannot log into database

Vidhya Sagar

mrroofer wrote:

Yes, you are right. But I don't know how to fix this.

Please help

Thank you so much


Hi mrroofer,

If you find any mismatch ID's from the code given above then you can use the below command to correct it.

Exec sp_change_users_login @Action = 'Update_One',@UserNamePattern = username , @LoginName = username.

where update_one -- maps the user to the login in SQL, if the user doesn't exists as a login in SQL it won't fix this. so you can use the below parameter
auto_fix - if you use this option it will map the user to the login in SQL, if the user doesn't exists as a login in SQL then it will create a login with the same name.

Refer the below link for more..
http://msdn2.microsoft.com/en-us/library/ms174378.aspx