February 12, 2008 at 5:49 pm
Hi all,
How can i drop all users at once.
I hope i can get all users by doing bellow query: please correct me if i am wrong
SELECT name FROM sys.database_principals
where type_desc like '%use%'
and name not in('dbo','guest','INFORMATION_SCHEMA','sys','public')
how can i drop all users from restored database!!
Thanks
February 14, 2008 at 8:50 pm
Create a cursor for the users in the database (omitting any users you do not wish to drop) assign it to a variable @user and then use as follows: EXEC sp_dropuser @user
- Tim Ford, SQL Server MVPhttp://www.sqlcruise.comhttp://www.thesqlagentman.com http://www.linkedin.com/in/timothyford
February 14, 2008 at 11:52 pm
Hi pat,
Just try this one. It may work ....:w00t:
ALTER PROCEDURE sp_DeleteUsers
AS
BEGIN
DECLARE @Name varchar(256),@Cnt int
SET @Cnt = 1
SET @Name = (SELECT Count(name) FROM sys.database_principals)
--WHERE type_desc like '%use%')
--AND name NOT IN('dbo','guest','INFORMATION_SCHEMA','sys','public'))
WHILE @Cnt <= @Name
BEGIN
DECLARE @User varchar(256)
SET @User = (SELECT Q.[name] FROM
(SELECT P.[name],P.RowNumber FROM
(SELECT NAME,ROW_NUMBER() OVER (ORDER BY [name]) AS 'RowNumber'
FROM sys.database_principals) AS P WHERE P.RowNumber = @Cnt) AS Q)
EXEC sp_dropuser @User
SET @Cnt = @Cnt + 1
END
END
EXEC sp_DeleteUsers
-----
February 14, 2008 at 11:55 pm
change the type_desc according to your's.:)
February 15, 2008 at 4:38 am
you mention you want to do this in a restored database. If this is because the users have become orphaned use 'sp_change_users_login' to re-synch them rather than drop\recreate.
If thats not the reason ignore me.:)
---------------------------------------------------------------------
March 29, 2010 at 7:54 am
The examples quoted above did not function correctly and we have rewritten it using a cursor to get the results we needed .....
This will leave all of the default perms (public,dbo,guest,information_schema,sys) and any fixed roles (db_ddladmin etc,etc)
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_DeleteUsers]
AS
DECLARE @UserName varchar(256)
DECLARE csrUser CURSOR FOR
SELECT
[name]
FROM
sys.database_principals
WHERE
principal_id >4
AND
is_fixed_role <1
ORDER BY
[name]
OPEN csrUser
FETCH NEXT FROM csrUser INTO @UserName
WHILE @@FETCH_STATUS <> -1
BEGIN
EXEC sp_revokedbaccess @UserName
FETCH NEXT FROM csrUser INTO @UserName
END
CLOSE csrUser
DEALLOCATE csrUser
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply