November 4, 2008 at 1:44 pm
I recently restored my databases to a new environment, when I try to log into the databases using the login associated with the databases it fails.
Message: Cannot open user default database. Login failed. Login failed for user , (Microsoft SQL, Error: 4064)
Any ideas?
November 4, 2008 at 1:58 pm
Hi,
It is because the logins reside in the master database. Did you restore into a SQL Server that is not used by anyone?
In that case you can do a backup of the master from the original environment and restore into the new environment.
Otherwise you have to script the logins from original environment; if they are not too many you can just right-click and choose "Script Login as".
If they are many, there is a script here http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=161433 that will make it a little bit less cumbersome for you.
HTH!
/Elisabeth
elisabeth@sqlserverland.com
MCITP | MCT
http://sqlblog.com/blogs/elisabeth_redei/
http://linkedin.com/in/elisabethredei
November 4, 2008 at 2:21 pm
did you resync the users ?
at db level execute this to get an overview of the actions you need to take:
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' -- y or no to exclude
--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') -- enkel aanvullen indien je een ID niet wenst de synchroniseren
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'
--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
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply