I've done this a million times and every time I forget how. When you restore a database from a backup you are typicaly left with some users that aren't linked correctly to their login. To detect these users you can run this sproc:
sp_change_users_login
@Action='Report';
This will give you a listing of all the orphaned users. You can then use this call to fix them:
sp_change_users_login
@Action='update_one', @UserNamePattern='<database_user>', @LoginName='<login_name>'
Another common problem I've had is some of these users I just want to drop. When I call drop user <blah> I get an error message that reads:
The database principal owns a schema in the database, and cannot be dropped.
To fix this you first have to find out which schemas the user owns:
select
s.* from sys.schemas as s
inner join sys.database_principals as dp on dp.principal_id = s.principal_id
where
dp.[name] = '<login_name>';
Once you know that, you should also check to see if there are any objects associated with that schema. My guess is that there aren't.
select
s.[name] as schemaname, o.[name] as objectname, o.type_desc
from sys.objects as o
inner join sys.schemas as s on s.schema_id = o.schema_id
inner join sys.database_principals as dp on dp.principal_id = s.principal_id
where dp.[name] = '<login_name>'
order by s.[name], o.type_desc, o.[name];
If there aren't any objects, then you can drop the schema
drop
schema <schema_name>
Once you are done with that, you should be able to drop the user.
posted on Tuesday, June 27, 2006 3:13 PM