User already exists in the current database but without LOGIN
From: JJA (johna_at_cbmiweb.com)
Date: 01/26/05
- Next message: xanthviper_at_xanthviper.com: "Data Encryption: Searching Encrypted Data"
- Previous message: sam: "Re: Password's case sensitivity"
- Next in thread: JD: "Re: User already exists in the current database but without LOGIN"
- Reply: JD: "Re: User already exists in the current database but without LOGIN"
- Messages sorted by: [ date ] [ thread ] [ subject ] [ author ] [ attachment ]
Date: 26 Jan 2005 09:56:00 -0800
I upgraded my SQL Server 7.0 database, MYPROD, by detaching it from
SERVERNT, copying all its NT files to my new SERVER2003, and then I
EXECUTEd sp_attach_db to make it known to my default instance of SQL
Server 2000 (running on Windows Server 2003).
Thru Ent Manager, I notice that the Users entry for this database shows
MYWEBUSER as the USERNAME, <NONE> for the LOGIN name, and when I click
on the entry in the grid, the permissions are checked for PUBLIC and
DBO (both of which I want).
I have a perfectly good LOGIN called MYWEBUSER already in place on
SERVER2003 (and also on the old SERVERNT) but somehow in the process of
copying the files the relationship of this USER to a LOGIN has not been
copied over (i.e. this user entry has nothing for LOGIN).
So I tried this:
USE MYPROD
GO
EXEC sp_grantdbaccess @loginame = 'mywebuser'
Server: Msg 15023, Level 16, State 1, Procedure sp_grantdbaccess, Line
147
User or role 'mdwebuser' already exists in the current database.
I've looked around a lot and cannot seem to find how to script this
out. I am trying to avoid:
a. manual DELETE of the USER entry from MYPROD
b. manual visit to LOGINs, click on MYWEBUSER, and click of MYPROD
database
(step b seems to add MYWEBUSER into MYPROD with MYWEBUSER as a valid
LOGIN)
I know USER entry is different from the LOGIN entry. Is my problem due
to the fact that they are named the same? I can fix this manually but
would like to build a script to be run after this COPY with UPGRADE
operation. Thank you in advance for help.
- Next message: xanthviper_at_xanthviper.com: "Data Encryption: Searching Encrypted Data"
- Previous message: sam: "Re: Password's case sensitivity"
- Next in thread: JD: "Re: User already exists in the current database but without LOGIN"
- Reply: JD: "Re: User already exists in the current database but without LOGIN"
- Messages sorted by: [ date ] [ thread ] [ subject ] [ author ] [ attachment ]
Relevant Pages
|
