Link to home
Start Free TrialLog in
Avatar of LyndaPostal
LyndaPostalFlag for United States of America

asked on

SQL 2005 ... Login name must be specified

Hi Gurus
I used MSDE and SQL Server 2000 Enterprise manager to run local tests on my web applications before I upload to the production server. My new machine (HP Laptop) won't install Enterprise Manager for SQL 2000.

So I installed SQL Server 2005.
Here is the problem ... I cannot seem to recreate my logins. I used to just drop the login and recreate so I could create the local login (which NEVER seems to come across when I re-attach the database)

I try to drop the Login ... I get "The database principal owns a schema in the database, and cannot be dropped."

OK ... fine, I uncheck 'db_owner' in the database and I get: Login name must be specified. (SqlManagerUI)
TITLE: Microsoft SQL Server Management Studio

Well, I cannot enter a login name (that field is grayed out), I cannot drop the login because it is a db_owner, I cannot change the properties of the login because it has no login name and I cannot give it a login name ...

I found something about this at
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=558742&SiteID=1

I run the query shown there and I, too, get a NULL result set. I know I can just create new users but to have to do this for the 25 databases I have is just wierd.

There has got to be an easier way, this is nutz :(

Any advice?

:) Lynda
Avatar of puranik_p
puranik_p
Flag of India image

I have been working with SQL 2005 for the last 12 monthsb and this seems to be the only solution.
Avatar of Aneesh
Hi LyndaPostal,

In order to change the database owner use the following

Use urDB
exec sp_changeDBOwner 'newOwnerName'

Cheers!
Avatar of LyndaPostal

ASKER

I don't need to change the dbo, I need to drop a login (that has no login name becuase SQL 2005 will not pick that up from an attached SQL 2000 database) and re-create the login with all the correct information. Is that possible?

I've tried everything and I'm afraid that puranik_p is correct. Create a new login and use that in my web.config. this is just a real pain and there does not seem to be a real solution. IMHO, M$FT really screwed the pooch on this one ...

:( Lynda
ASKER CERTIFIED SOLUTION
Avatar of puranik_p
puranik_p
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of imran_fast
imran_fast

Hi LyndaPostal,
You cannot drop the login simply because it owns some object. to drop login you first need to move the object to other users ownership before dorping it.

come back to your orignal question
use queries to add login instead of doing it from the UI try using
EXEC sp_addlogin 'Loginname', 'passwork'
I can't drop the login becuase there is no Login Name ... (and because it owns some object, although according to Red Gate's sql dependancy tracker ... the login owns nothing.

this is *())(*)()(&%$#$%$# in a big way ... and now I'm getting the error:

Cannot connect to 127.0.0.1.

------------------------------
ADDITIONAL INFORMATION:

An error has occurred while establishing a connection to the server.  When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server) (Microsoft SQL Server, Error: 2)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=2&LinkId=20476 (this is useless)

new logins it is, as soon as I can resolve the failure to login problem ... all I want to do is get some work done, not have to fight with my data store

puranik_p: you win ... and suggestions on my new problem ... ME logging in to MY LOCAL machine ... this is just BS.

Lynda


Run the following stored procedure in the database where the user cannot login. Change the ***** to the username (both are the same). What this is really doing is mapping the login from the database (in the first set of *****) to the login from SQL Server. This worked under SQL Server 2000 and works under 2005 and 2008 as well.
exec sp_change_users_login 'Update_One', '*****', '*****'
I wish I knew this back then. I searched and found the documentation on this procedure and it says...
This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature. Use ALTER USER  instead.

Posting here just in case somebody stumbles on this.