Problem in Enterprise Manager - 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

Old Forums

CLOSED - General SQL Server

Problem in Enterprise Manager

Author

Topic

yasin
Yak Posting Veteran

68 Posts

Posted - 2005-03-16 : 09:29:49

hello

I Detached the user default database from the server (Using EM) and when i was attaching the database,there is a problem. Enter prise Manager is working but In a servergroup, a particular server is not opened(Expand). It shows the error as cannot open user default database, Login failed, i entered in EM using sa login.

Please any one can help me in this regards.

thanx in advance.

by

yasi

Kristen
Test

22859 Posts

Posted - 2005-03-16 : 09:40:40

Most likely thing is that the "broken" server is configured with a Default Database, for your user login, that no longer exists.

You'll need to log into that server with a different user ID (e.g. "sa") and change the default database for the User ID you normally connect with from E.M.

Kristen

yasin
Yak Posting Veteran

68 Posts

Posted - 2005-03-16 : 09:48:28

Mr. Kristen

If i need to enter different login id means how i can, Because that particular server is not drapping down in EM.

Then how i can change the userdefault database. But All user are working with that server smoothly.I entered in using sa login. but it shows the error like that. Then what i will do in this case.

thanx a lot

by
yasi

yasin
Yak Posting Veteran

68 Posts

Posted - 2005-03-16 : 10:33:48

Yes Mr. Kriston.

Now i got it. it enters inside the query analyzer, then how i can change default database. Please ...

Thanx lot ma . Thanx

by
yasi

mr_mist
Grunnio

1870 Posts

Posted - 2005-03-16 : 10:37:33

Use sp_defaultdb

-------
Moo. :)

yasin
Yak Posting Veteran

68 Posts

Posted - 2005-03-16 : 11:19:27

Hello Mr.Mist

i had run the query in the query analyzer(Under System as well as user db), but it shows the error as

Cannot change default database belonging to someone else

Thanx lot.

by
yasin

mr_mist
Grunnio

1870 Posts

Posted - 2005-03-16 : 11:43:21

You need to be logged in as a system administrator to change another user's default database.

-------
Moo. :)

yasin
Yak Posting Veteran

68 Posts

Posted - 2005-03-16 : 11:51:38

Mr.Mr_Mist

I entered as admin but not sa. If i use sa it will enter into query analyzer and it shows the error as Cannot open user default database, Login failed. That is the problem. Then help to solve this problem.

thanx in advance.

with warm regards
yasi

yasin
Yak Posting Veteran

68 Posts

Posted - 2005-03-16 : 11:53:26

Mr.Mr_Mist

I entered as admin but not sa. If i use system administrator it will not enter into query analyzer and it shows the error as
Cannot open user default database, Login failed.

That is the problem, how i can change the user default database. Then help to solve this problem.

thanx in advance.

with warm regards
yasi

Kristen
Test

22859 Posts

Posted - 2005-03-16 : 11:56:06

Create a new database with the name of the now-missing database?

Kristen

yasin
Yak Posting Veteran

68 Posts

Posted - 2005-03-16 : 12:16:08

Mr. Kristen
i execute the query in query analyzer for creating database.

USE master
GO
CREATE DATABASE Sales
ON
( NAME = Sales_dat,
FILENAME = 'c:\program files\microsoft sql server\mssql\data\saledat.mdf',
SIZE = 10,
MAXSIZE = 50,
FILEGROWTH = 5 )
LOG ON
( NAME = 'Sales_log',
FILENAME = 'c:\program files\microsoft sql server\mssql\data\salelog.ldf',
SIZE = 5MB,
MAXSIZE = 25MB,
FILEGROWTH = 5MB )
GO

but it shows the error as

Server: Msg 262, Level 14, State 1, Line 1
CREATE DATABASE permission denied in database 'master'.

Kristen
Test

22859 Posts

Posted - 2005-03-16 : 12:21:26

Doesn't look like the account you logged on with has enough permissions to create a DB :-(

Note to self: Keep the default database for "sa" as MASTER !

Kristen

yasin
Yak Posting Veteran

68 Posts

Posted - 2005-03-16 : 12:27:21

Yes Mr. Kristen

If i use the sa account it will not enter inside(Query Analyzer) the login because it shows the error as i told before . Then how i can grant permission for sa.

Thanx a lot ma ... thanx in advance.

warm regards
yasi

yasin
Yak Posting Veteran

68 Posts

Posted - 2005-03-16 : 12:31:40

Mr. Kristen

In Enterprise manager i can't enter in a particular server in a server group without changing of the default database even if i use the system administrator account.

then how.

thanx a lot.

with regards
yasi.

Kristen
Test

22859 Posts

Posted - 2005-03-16 : 13:02:48

You need a user who has sysadmin permissions, and who is NOT sa, and who is NOT using the now-deleted-database as their default to create the missing DB.

Are you "sysadmin" or just a database owner ??

Failing that I'd paid the Microsoft PSS charge to get them to help you.

Or backup all the database, and re-install SQL Server and restore the DBs :-(

Kristen

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-03-16 : 13:21:11

If you haven't deleted the BUILTIN\Administrators group from SQL Server and it still has sysadmin fixed server role, then log onto the server using a Windows account and one that is a member of the local administrators group. Then, connect to Query Analyzer using Windows authentication. Next, run sp_defaultdb to change the default database for sa to master. Then you should be set.

If you have deleted the BUILTIN\Administrators group from SQL Server, then we'll have to get a little trickier to fix this. So let us know how this goes.

Tara

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2005-03-16 : 13:54:27

lol, people...

see OSQL utility in BOL or run QA from cmd window with -dxxx option.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-03-16 : 13:58:27

Where do you set the -d option in Query Analyzer?

I figured osql.exe is too advanced for this poster.

Tara

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2005-03-16 : 14:56:07

C:\MSSQL7\Binn\isqlw.exe -dmaster