Clicky

We have been doing restores of DB Microsoft SQL 2005 and found that the DB has a lot users.  We need to delete them.  

We can go about it one by one and delete them via MS SQL Studio, but for each DB its around 45 users.

Is there a script that we can run to delete users from a database?

asked 12/17/2011 05:02

Ramante's gravatar image

Ramante ♦♦


5 Answers:
You can make use of <databasename>.dbo.sysusers and following script. Prepare your script that will loop through the users and pick them up , run the below statement and drop the users.

use <databaseName>
go

drop user <username>
go
link

answered

TempDBA's gravatar image

TempDBA

Works!  can you give us an example for a loop?
link

answered 2011-12-18 at 02:00:42

Ramante's gravatar image

Ramante

A simple query is here.

DECLARE @User_tbl TABLE(UserName VARCHAR(MAX))

INSERT INTO @User_tbl
SELECT 'drop user ' + NAME FROM sysusers s  -- put your filtering conditions here

SELECT * FROM @User_tbl

Take the output and run in another window. Or you can make it more generic. But I am not connected to the server now.
link

answered 2011-12-18 at 02:04:04

TempDBA's gravatar image

TempDBA

Thank you very much, we got the idea.
link

answered 2011-12-18 at 02:11:22

Ramante's gravatar image

Ramante

Thanx
link

answered 2011-12-18 at 02:21:10

Ramante's gravatar image

Ramante

Your answer
[hide preview]

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Tags:

×4

Asked: 12/17/2011 05:02

Seen: 329 times

Last updated: 12/17/2011 06:22