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 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy