LyndaPostal
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
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
I have been working with SQL 2005 for the last 12 monthsb and this seems to be the only solution.
Hi LyndaPostal,
In order to change the database owner use the following
Use urDB
exec sp_changeDBOwner 'newOwnerName'
Cheers!
In order to change the database owner use the following
Use urDB
exec sp_changeDBOwner 'newOwnerName'
Cheers!
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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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'
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'
ASKER
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
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', '*****', '*****'
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.
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.