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, October 6, 2014 11:20 AM
Points: 163, Visits: 575
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


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Monday, November 17, 2014 12:50 PM
Points: 13,872, Visits: 9,598
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, October 6, 2014 11:20 AM
Points: 163, Visits: 575
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


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Monday, November 17, 2014 12:50 PM
Points: 13,872, Visits: 9,598
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