Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Drop multiple users on a single database Expand / Collapse
Author
Message
Posted Tuesday, October 29, 2013 8:05 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, May 6, 2014 10:23 AM
Points: 23, Visits: 357
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!

Post #1509364
Posted Tuesday, October 29, 2013 11:10 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Today @ 11:50 AM
Points: 258, Visits: 465
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

Post #1509482
Posted Tuesday, October 29, 2013 11:19 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, May 6, 2014 10:23 AM
Points: 23, Visits: 357
Matt - thank you.

I will check this out shortly.
Post #1509486
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse