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

Query to Delete all User defined schemas, roles and users Expand / Collapse
Author
Message
Posted Monday, February 21, 2011 9:57 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, July 28, 2014 12:06 PM
Points: 163, Visits: 573
Hi Is there any Query to delete all User defined schemas, roles and users in database .Thanks in Advance :)
Post #1067181
Posted Monday, February 21, 2011 12:03 PM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Friday, June 27, 2014 12:43 PM
Points: 15,444, Visits: 9,596
You could write one, using various system views, a couple of cursors, and some light-weight dynamic SQL.

However, I have to question "deleting all user-defined users" in the database. That'll pretty much get rid of all user accounts except guest and sa.


- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Post #1067245
Posted Tuesday, February 22, 2011 7:29 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, July 28, 2014 12:06 PM
Points: 163, Visits: 573
Hi Is there any query as such to remove all users except sa and system account users ?
Post #1067604
Posted Tuesday, February 22, 2011 8:46 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Friday, June 27, 2014 12:43 PM
Points: 15,444, Visits: 9,596
Something like:

select 'drop user ' + name + ';'
from sys.database_principals
WHERE type = 's';

Copy-and-paste the results into a window in Management Studio, delete the ones you want to keep, and then run it.


- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Post #1067657
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse