Query to Delete all User defined schemas, roles and users

Question

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

GSquared SSC Guru Points: 260824 More actions · Answer 1

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

Steve Dell SSCarpal Tunnel Points: 4623 More actions · Answer 2

Hi Is there any query as such to remove all users except sa and system account users ?

GSquared SSC Guru Points: 260824 More actions · Answer 3

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