URGENT!! Logins - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

URGENT!! Logins

Author

Topic

coolerbob
Aged Yak Warrior

841 Posts

Posted - 2005-04-28 : 08:18:24

I detached and then attached a database on the same server
I seem to have lost all the logins to the database other than SA
does anyone know why this happend and and i can get it back?

does attach not re-attach the logins? There's a sql dmo linkedServerLogin Obj. Is that what I need to use?
Isnt there just a re-attach logins sp?

coolerbob
Aged Yak Warrior

841 Posts

Posted - 2005-04-28 : 08:23:10

I get this error from the client side now:
[SqlException: Cannot open user default database '<ID>'. Using master database instead.
Changed database context to 'master'.
Changed language setting to us_english.]

Does that help?
this link:http://support.microsoft.com/default.aspx?scid=kb;en-us;307864&Product=sql
says that you can have this problem if you have detahced the database
but i have attached it again - so it surely shouldnt be a problem?!

coolerbob
Aged Yak Warrior

841 Posts

Posted - 2005-04-28 : 08:35:21

I've run sp_defaultdb for the logins and it came back succesfully
but when the users try and login, they still get the same problem
why is this happening?
PLEASE help?!

CanadaDBA

583 Posts

Posted - 2005-04-28 : 08:35:28

I am not sure why you get the error but you can modify the following and I think it helps.


USE <database name>
GO

-- Do this for users who are DB owner
exec sp_dropuser '<Login1>'
exec sp_grantdbaccess '<Login1>','<Login1>'
exec sp_addrolemember 'db_owner','<Login1>'

-- Do this for users who have data read and write rights (repeate it for any users you have)
exec sp_dropuser '<Login2>'
exec sp_grantdbaccess '<Login2>','<Login2>'
exec sp_addrolemember 'db_datawriter','<Login2>'
exec sp_addrolemember 'db_datareader','<Login2>'

-- This part grants EXE rights to a specific login (repeate it for any users you have)
declare @objname varchar(100)

declare namelist  insensitive cursor for
select name
from sysobjects
where xtype in('p','fn')
and category=0

open namelist
fetch next from namelist into @objname
while @@fetch_status=0
begin
	print @objname
	exec('grant execute on [dbo].[' + @objname + '] to [Login2]')
	fetch next from namelist into @objname
end

close namelist
deallocate namelist
Go

Canada DBA

coolerbob
Aged Yak Warrior

841 Posts

Posted - 2005-04-28 : 08:39:28

stop press! sp_defaultdb did work
there might have been a caching issue or "persist security" connection setting issue there that explained why it didnt work emmediately.
so why does detach cause users to lose their default db for their login? MS says that's "by design". But isnt that Bad design?

coolerbob
Aged Yak Warrior

841 Posts

Posted - 2005-04-28 : 08:41:00

thanks farhadr, I'll keep that script close in case I need it in future
Do you have an opinion on my question above?

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-04-28 : 09:46:59

>>so why does detach cause users to lose their default db for their login?

If the default database is the database that was detached (or dropped), then the default database column in master..sysLogins is updated to NULL for all logins that have the detached database as their default database. This must happen because the default database has been removed what with foreign key constraints and all...

Be One with the Optimizer
TG

coolerbob
Aged Yak Warrior

841 Posts

Posted - 2005-04-28 : 09:55:14

ah, taht makes sense i guess

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources