Back in February, I had a problem removing a user from a role and
ended up deleting and re-creating the user.

Now, on another server in a different environment, I am having the
same problem.

History:
We added a new login ("fred") and added them to a database
(exampleDB).

Then, we added the user to db_datareader/writer in exampleDB.

A new role was created ('XYZ") in exampleDB and we added the user to
the role. We added the role to db_datareader/writer.

Now, when we try to remove "fred" from db_datareader/writer, it
appears that he's being dropped, but when we go back into the role,
he's still there. We've dropped him using the GUI, via TSQL, from the
role properties, from the login properties, etc.

I can see the sp_droprolemember execute in profiler, but I don't see
it added back in. The user was not logged in at the time of the
attempt.

I've run sp_change_users_login 'Report' and got no results, either.

I can add/remove them from roles that I've created. I can add/remove
them from db_accessadmin.

Microsoft SQL Server 2005 - 9.00.3175.00 (Intel X86) Jun 14 2007
09:20:57 Copyright (c) 1988-2005 Microsoft Corporation Enterprise
Edition on Windows NT 5.2 (Build 3790: Service Pack 2)

Any suggestions would be appreciated.

Some more information:

If I drop the new role from db_datareader, the user gets dropped from
the db_datareader role, too. If I add the role back in, the user gets
added in themselves, too.

I emptied/dropped the new role, and re-created it, then added the user
back in, and when I add it to db_datareader, the user gets added too
(so that the role AND the user appear in db_datareader).





On Jun 12, 10:12am, Chris <cbtechli...@gmail.com> wrote:
> Back in February, I had a problem removing a user from a role and
> ended up deleting and re-creating the user.
>
> Now, on another server in a different environment, I am having the
> same problem.
>
> History:
> We added a new login ("fred") and added them to a database
> (exampleDB).
>
> Then, we added the user to db_datareader/writer in exampleDB.
>
> A new role was created ('XYZ") in exampleDB and we added the user to
> the role. We added the role to db_datareader/writer.
>
> Now, when we try to remove "fred" from db_datareader/writer, it
> appears that he's being dropped, but when we go back into the role,
> he's still there. We've dropped him using the GUI, via TSQL, from the
> role properties, from the login properties, etc.
>
> I can see the sp_droprolemember execute in profiler, but I don't see
> it added back in. The user was not logged in at the time of the
> attempt.
>
> I've run sp_change_users_login 'Report' and got no results, either.
>
> I can add/remove them from roles that I've created. I can add/remove
> them from db_accessadmin.
>
> Microsoft SQL Server 2005 - 9.00.3175.00 (Intel X86) Jun 14 2007
> 09:20:57 Copyright (c) 1988-2005 Microsoft Corporation Enterprise
> Edition on Windows NT 5.2 (Build 3790: Service Pack 2)
>
> Any suggestions would be appreciated.