May 8, 2008 at 1:48 pm
Here's the situation. Our last DBA upgraded one of our servers to 2005. He missed one login, and we're just finding out about this now. So, I tried to recreate the user and test the connection. Trying to connect I received the 'cannot open user default database'. Upon examination, I was not able to see the properties of the database or any database. It appeared that the last DBA had made himself the owner of all the databases.
So I switched over ownership to 'sa', just to get access again. Now, I can view the database. I dropped and recreated the login from master and the default database. Although it didn't look like the user existed in the default database anyway.
Again, I tried to create the login using the CREATE LOGIN statement. The statement ran successfully and the user appeared in management studio, with the correct default db, but I still received that error.
I've tried adding permissions for the login through database properties, but don't see the user when I look at the available users.
I've also tried the old sp_addlogin to create the login, but still no avail.
Does anyone have any idea what might be going on? Any help would be greatly appreciated.
Thanks
May 8, 2008 at 2:23 pm
Did you run CREATE USER ... FOR LOGIN command and then GRANT CONNECT ?
Alternatively, you can add new user using GUI, it won't be shown by in list of users if you create login only.
Rgds,
Piotr
...and your only reply is slàinte mhath
May 8, 2008 at 2:37 pm
this script generates "user synchronisation" statements.
Maybe this will help out:
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' -- veranderen indien je geen windows accounts wil behandelen !
--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'
print '--*** exec stmt commented !!! ***'
-- NO 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 '** einde User-synchronisatie **'
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
May 8, 2008 at 2:38 pm
thanks for the reply.
I ran the CREATE USER statement but not the GRANT CONNECT statement. When I try to run the GRANT CONNECT statement I get the "Msg 15151, Level 16, State 1, Line 4
Cannot find the user 'pctech1', because it does not exist or you do not have permission"
I have sysadmin rights..
May 8, 2008 at 2:49 pm
thanks alzdba, but the script doesn't generate any results....
May 8, 2008 at 3:04 pm
What does this query return? does it contain the user you have created in the database you wanted?
select DB_NAME(), name from sys.sysusers
Piotr
...and your only reply is slàinte mhath
May 8, 2008 at 8:30 pm
thanks. I ran that query and the user is not included in the results
May 8, 2008 at 9:34 pm
Try running: sp_change_users_login 'Report' in the database where you are trying to add the user. If you find any users in the list, you need to run this procedure with one of the other available parameters to tie the user to the appropriate login.
Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
May 8, 2008 at 11:44 pm
You will have to execute the script with a connection to the dodgy database !
If that user was the actual database owner (<> member of db_owner group), it will not repair it !
You'll have to sp_changedbowner that database yourself !
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
May 9, 2008 at 6:11 am
Jeff thanks, the user did not appear in the results
ALZDBA, thanks again. One of the first things I did was change ownership of that database. I also reran your script and got some results. But again the user was not included in the results.
It appeared that for some reason, the user just was not getting created in that database. I circumvented the problem by just defaulting the user to master. The purpose of this particular user is installation of the database's corresponding application, hopefully they won't experience any functionality issues. we'll see.
Thanks again for all your help
May 9, 2008 at 7:02 am
As of my knowledge check whether that user is mapped to another login.
May 9, 2008 at 7:38 am
Drop the user and then recreate the user with CREATE USER FOR LOGIN and then user GRANT clause to grant the permissions.
Basit Ali Farooq
MCITP Database Administrator
Microsoft Certified Professional Developer (Web Applications)
Microsoft Certified Database Administrator
Microsoft Certified Systems Engineer
Microsoft Certified Systems Administrator
CIW Security Analyst
Cisco Certified Network Associate
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply