Hi
I'm trying to write a script that will make sure the user accounts are set up correctly after de attaching a database moving it to another machine and reattaching it. The problem is the user example1 had a login Name of example1 but after I've copied it over and re attached it the user example1 has No user at all, throught EM all I have to do is Delete the Database user and recreate it correctly, but We need to do this as part on a script, However I don't seem to be able to do the same thing through SQL.
The way I was attempting to drop and recreate the user was like this:
Execute sp_dropuser example1
Execute sp_addlogin example1
Execute sp_adduser example1
Execute sp_addrolemember @.rolename = db_datareader, @.membername = example1
Execute sp_addrolemember @.rolename = db_datawriter, @.membername = example1
However it comes back with this error:
Server: Msg 15175, Level 16, State 1, Procedure sp_droplogin, Line 93
Login 'example1' is aliased or mapped to a user in one or more database(s). Drop the user or alias before dropping the login.
Server: Msg 15025, Level 16, State 1, Procedure sp_addlogin, Line 56
The login 'example1' already exists.
Does any one now how I can get it too work like the EM version!
My eternal gratitude, NixiesYou need to exec sp_grantdbaccess first before you do exec sp_addrolemember.|||Originally posted by joejcheng
You need to exec sp_grantdbaccess first before you do exec sp_addrolemember.
Just doubled checked, dbaccess is fine and the sp_addrolemember went through with out any errors. My big problem is that I can't drop the user to recreate them. Any ideas?|||I had a similar problem a while back and was able to use the procedures from Microsoft to fix. Check out:
http://support.microsoft.com/default.aspx?scid=kb;EN-US;298897.
Basically, you need to resynchronize the login SID's before you can perform any security edits. Hope this helps.
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment