fraggle i just tested this myself;

i was hoping i could alter a user and remove their mapping to the existing login, but i found i had to crop and recreate the user without login instead.

if the user doesn't have a matching user, it wouldn't have access to that specific database anymore.

this was my test:

Create LOGIN [ClarkKent] WITH PASSWORD='NotTheRealPassword'

USE [WHATEVER]

Create USER [ClarkKent] FOR LOGIN [ClarkKent] ;

--didn't work:

--ALTER USER [ClarkKent] WITHOUT LOGIN;

--remove and put the user back without login, so we can

DROP USER [ClarkKent];

CREATE USER [ClarkKent] WITHOUT LOGIN;

--add back any roles, ie

CREATE ROLE [AlmostOwners]

EXEC sp_addrolemember N'db_ddladmin', N'AlmostOwners'

EXEC sp_addrolemember N'db_datareader', N'AlmostOwners'

EXEC sp_addrolemember N'db_datawriter', N'AlmostOwners'

--can the users EXECUTE procedures? comment out if false

GRANT EXECUTE TO [AlmostOwners]

--allow the users to see view proc and function definitions

Grant View Definition ON SCHEMA::[dbo] To [AlmostOwners]

--finally add our user to the role:

EXEC sp_addrolemember N'AlmostOwners', N'ClarkKent'

if needed then later we can ALTER USER [ClarkKent] FOR LOGIN [ClarkKent] to put him back.