Wednesday, March 7, 2012

Problem with droping users, please help

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.

No comments:

Post a Comment