removing database access for login

I created a new login and accidentally added a user to the wrong
database using.

USE [wrongDatabase]
CREATE USER [Bob] FOR LOGIN [Bob]
EXEC sp_addrolemember N'db_owner', N'Bob'


I thought the TSQL below would remove that user, but I find that user
can still connect to the database as dbo.

USE [wrongDatabase]
EXEC sp_droprolemember N'db_owner', N'Bob'
DROP USER [Bob]

What am I missing?

TCorp ( ) writes:
> I created a new login and accidentally added a user to the wrong
> database using.
>
> USE [wrongDatabase]
> CREATE USER [Bob] FOR LOGIN [Bob]
> EXEC sp_addrolemember N'db_owner', N'Bob'
>
>
> I thought the TSQL below would remove that user, but I find that user
> can still connect to the database as dbo.
>
> USE [wrongDatabase]
> EXEC sp_droprolemember N'db_owner', N'Bob'
> DROP USER [Bob]
>
> What am I missing?

It seems that Bob is either owner of the database, or is a member of
the server role sysadmin.


--
Erland Sommarskog, SQL Server MVP, esquel DeleteThis @sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx