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 12»»

Drop All Users in the Database Expand / Collapse
Author
Message
Posted Monday, March 16, 2009 10:13 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Saturday, April 3, 2010 2:42 PM
Points: 1,023, Visits: 1,893
Is there a way to drop all users in a particular database instead of deleting each.
Post #676687
Posted Monday, March 16, 2009 11:18 AM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Monday, July 7, 2014 1:15 PM
Points: 750, Visits: 3,157
If you mean instead of deleting each individually, then you have 2 choices -
1) write a script
2) use the Object Explorer Details pane in SSMS to list the users, highlight those you want to delete, right click and select delete

It really depends on whether this is a one-off removal or for some repeated use.

I can expand on (1) if that is the direction you want to take.
Post #676746
Posted Monday, March 16, 2009 11:30 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Yesterday @ 2:28 PM
Points: 33,062, Visits: 15,174
I might suspect that the services are not started then. You want to use SQL tools, not the Windows ones to work with SQL Server.

Moving to SQL 2005 Admin







Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #676765
Posted Monday, March 16, 2009 11:30 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Yesterday @ 2:28 PM
Points: 33,062, Visits: 15,174
This sounds like something that would be fun, reduce work load, reduce phone calls. Wouldn't you want to enjoy this and delete them one by one :D






Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #676768
Posted Monday, March 16, 2009 11:42 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:43 AM
Points: 12,880, Visits: 31,803
i just slapped this together.
i'm using sp_revokeaccess instead of sp_dropuser, because the users might be orphaned and not tied to a login;
this yanks them out the way i expected:
ALTER PROCEDURE sp_DeleteUsers(@DeleteThem INT = 0)
AS
BEGIN
  DECLARE
@username VARCHAR(64)
  
DECLARE c1 CURSOR FOR
    SELECT
name  
    
FROM sysusers
    
WHERE name NOT IN('dbo','guest','INFORMATION_SCHEMA','sys','public')
      AND
LEFT(name,3) <> 'db_'
  
OPEN c1
  
FETCH next FROM c1 INTO @username
  
WHILE @@fetch_status <> -1
  
BEGIN
      PRINT
'Dropping ' + @username
      
IF @DeleteThem <> 0
        
EXEC dbo.sp_revokedbaccess @username
    
FETCH next FROM c1 INTO @username
  
END
CLOSE
c1
DEALLOCATE c1
END





Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #676786
Posted Monday, March 16, 2009 12:04 PM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 6:25 PM
Points: 3,121, Visits: 11,397
Lowell (3/16/2009)
i just slapped this together.
i'm using sp_revokeaccess instead of sp_dropuser, because the users might be orphaned and not tied to a login;
this yanks them out the way i expected:
ALTER PROCEDURE sp_DeleteUsers(@DeleteThem INT = 0)
AS
BEGIN
  DECLARE
@username VARCHAR(64)
  
DECLARE c1 CURSOR FOR
    SELECT
name  
    
FROM sysusers
    
WHERE name NOT IN('dbo','guest','INFORMATION_SCHEMA','sys','public')
      AND
LEFT(name,3) <> 'db_'
  
OPEN c1
  
FETCH next FROM c1 INTO @username
  
WHILE @@fetch_status <> -1
  
BEGIN
      PRINT
'Dropping ' + @username
      
IF @DeleteThem <> 0
        
EXEC dbo.sp_revokedbaccess @username
    
FETCH next FROM c1 INTO @username
  
END
CLOSE
c1
DEALLOCATE c1
END





Here is another way to do that:
declare @sql nvarchar(max)
set @sql = ''

SELECT @sql = @sql+
'
print ''Dropping '+name+'''
execute master.dbo.sp_revokedbaccess '''+name+'''
'
FROM
dbo.sysusers
WHERE
name NOT IN('dbo','guest','INFORMATION_SCHEMA','sys','public')
AND LEFT(name,3) <> 'db_'
order by
name

execute ( @sql )


Post #676811
Posted Friday, May 20, 2011 3:18 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, June 14, 2012 11:00 AM
Points: 1, Visits: 160
Hi,

Thank you for posting the script.

But in my case when i execute the above script, it generate Drop statments for all users in given database along with the database role.
i.e it creates drop statment for user defined db role, which won't allow me to drop.

Is there any way to exclude user defined db role from users in the script?


DJSH
Post #1112225
Posted Friday, May 20, 2011 5:38 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:43 AM
Points: 12,880, Visits: 31,803
can't you just add to the WHERE statement to exclude items you don't want to include? like AND issqlrole = 0 or something?

Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1112283
Posted Friday, May 20, 2011 6:17 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 2:48 AM
Points: 6,167, Visits: 13,310
also you want to be using the sys.database_principals catalog view and not sysusers. This should be better

declare @sql nvarchar(max)
set @sql = ''

SELECT @sql = @sql+
'
print ''Dropping '+name+'''
execute master.dbo.sp_revokedbaccess '''+name+'''
'
FROM
sys.database_principals
WHERE
name NOT IN('dbo','guest','INFORMATION_SCHEMA','sys','public')
AND TYPE <> 'R'
order by
name

execute (@sql)



-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs"
Post #1112309
Posted Friday, July 29, 2011 5:25 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: 2 days ago @ 7:53 AM
Points: 365, Visits: 921
Or this - doesn't drop users who own a schema

declare @sql nvarchar(max)
set @sql = ''

SELECT @sql = @sql+
'
print ''Dropping '+name+'''
execute master.dbo.sp_revokedbaccess '''+name+'''
'
FROM
sys.database_principals
WHERE type <> 'R'
AND NOT EXISTS
(
SELECT 1
FROM sys.schemas s
WHERE s.principal_id = dp.principal_id
)
ORDER BY name

execute (@sql)

.. yes I know it was a couple of months back but I am using it today.
Post #1150822
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse