October 29, 2013 at 8:05 am
Good morning. We have a database that we need to keep around for historical purposes. I backed up and restored this database to another SQL Server 2005 server for this purpose. The thing is on the original server, this database had hundreds of users on it and their associated logins. On this 'new' server where I restored it, the logins do not exist - and I don't want to create the logins.
There will only be about a dozen people that need to access this archived database, so I would like to drop all but these dozen or so users from this archived database. I suppose I could just leave them as orphaned, but would like to clean them up. Deleting manually is a nightmare, a script would be more efficient.
I'm sure there's something out there, I just have been unable to find it during searches.
Thank you!
October 29, 2013 at 11:10 am
Assuming none of the users own schemas or objects, this will generate the commands for you. Edit as needed:
select 'drop user [' + d.name + ']'
from sys.database_principals d left join
sys.server_principals s on d.sid = s.sid
where d.type = 'U'
and s.sid is null
October 29, 2013 at 11:19 am
Matt - thank you.
I will check this out shortly.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply