April 15, 2014 at 2:36 pm
Hi I need your help. I cannot find the similar question so open a new topic.
I want to drop all users in a database A (2008r2), the database A is restored from a backup of database B. Two commands I can use:
sp_dropuser username;
-- or
drop user username;
but error happened when I used sp_dropuser to delete a user which login is a windows domain GROUP, see below for details. Users with login of windows domain USER can be deleted successfuly by the sp. The login domain\windowgroup is existing in database A as wll. the user is not orphanded user.
Msg 15008, Level 16, State 1, Procedure sp_dropuser, Line 12
User 'domain\windowgroup' does not exist in the current database.
But, the user can be deleted by using command DROP USER. I don't know why?
And I have a script as below, if I use DROP USER instead of sp_dropuser, syntax error happen.
Why sp_dropuser cannot be used to drop this kind of users? How to use DROP USER in script file?
Thanks
DECLARE @UserName sysname;
DECLARE User_Cursor CURSOR FOR
SELECT name FROM sys.database_principals
where type in ('U', 'S', 'G')
and principal_id > 4;
OPEN User_Cursor;
FETCH NEXT FROM User_Cursor into @UserName;
WHILE @@FETCH_STATUS = 0
BEGIN
exec sp_dropuser @UserName
FETCH NEXT FROM User_Cursor into @UserName
END;
CLOSE User_Cursor;
DEALLOCATE User_Cursor;
April 15, 2014 at 3:27 pm
I used another way to run DROP USER successfully in script, see below. But still don't know why sp_dropuser cannot be used to delete this kind of users.
DECLARE @UserName sysname;
DECLARE @sqlstr nvarchar(500);
DECLARE User_Cursor CURSOR FOR
SELECT name FROM sys.database_principals
where type in ('U', 'S', 'G')
and principal_id > 4;
OPEN User_Cursor;
FETCH NEXT FROM User_Cursor into @UserName;
WHILE @@FETCH_STATUS = 0
BEGIN
set @sqlstr = 'DROP USER [' + @UserName + ']'
execute sp_executesql @sqlstr
FETCH NEXT FROM User_Cursor into @UserName
END;
CLOSE User_Cursor;
DEALLOCATE User_Cursor;
April 18, 2014 at 6:19 am
My w.a.g. is that probably a brackets issue. Domain\User usually needs to be surrounded by square brackets [Domain\User] or it causes issues in the drop statements. It appears at first glance that your first section of code maybe didn't take that into account when pulling the name into the cursor.
While your second bit of code did.
April 21, 2014 at 7:39 am
Thanks for ur reply.
I have tried to execute this command : exec sp_dropuser [domain\groupname] in SSMS directly, but still failed. Please note what i wanted to drop was domain group, not domain user, and I have used square brackets.
April 21, 2014 at 7:56 am
It works the same way.
Edit: Just realized you were referring to a system proc, so deleting my previous statement. Google the error you received when you put in the square brackets.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply