2

Running SELECT * FROM dbName.dbo.sysusers ORDER BY name; reveals users that start with \, and this causes errors when try to add the same user without the slash.

Can't run EXEC sp_dropuser or DROP USER. This throws an error telling that user does not exist or you do not have permission.

Any ideas on how to remove the user? I believe that the \user is orphaned from a database migration from SQL Server 2000 or 2005. The current database is running on SQL Server 2016. I've tried using the GUI and T-SQL.

I've tried EXEC sp_dropuser and DROP USER using [\user], '\user',\user, QUOTENAME('\user').

I get either

Incorrect syntax

or

Cannot drop the user '\user', because it does not exist or you do not have permission.

errors.

Here is the row from the sysusers table.

uid              23
status           0
name             \User
sid              0x010500000000000512000000FD77B156780C942546170A3273170200
roles            NULL
createdate       2012-08-17 10:01:05.600
updatedate       2012-08-17 10:01:05.600
altuid           NULL
password         NULL
gid              0
environ          NULL
hasdbaccess      0
islogin          1
isntname         0
isntgroup        0
isntuser         0
issqluser        0
isaliased        0
issqlrole        0
isapprole        0
  • I've tried SSMS 2016 & 2017. – Julie Jun 13 '18 at 16:58
  • Have you tried using the GUI to generate the necessary SQL commands, to see if they produce something different than you are yourself? You may need to escape the backslash.... – RDFozz Jun 13 '18 at 17:15
  • 1
    try DROP USER N'\user' – Kin Shah Jun 13 '18 at 18:32
  • 1
    Are you sure that you are in the dbName DB when executing the DROP USER statement? Have you tried DROP USER [User]; (i.e. without the ``?). In 'dbo.sysusers', does this User have an SID? Can you update the question with the entire row from 'dbo.sysusers' for this particular User? – Solomon Rutzky Jun 13 '18 at 20:54
  • 1
    Hi @Julie and thanks for adding that info to the question. I was asking if you could try the DROP USER statement without using the back-slash to the left of User. So specifically just: DROP USER [User];, which so far you haven't mentioned trying. – Solomon Rutzky Jun 14 '18 at 20:29
2

I opened a support ticket with Microsoft. They gave me two options.

  1. Create a new AD account for the user
  2. Migrate the 2016 database to 2008, delete user, migrate back.

I also found I could give the user sysadmin rights and it then ignores the database user.

No further information from Microsoft. Support ticket closed.

  • Interesting. Thanks for following-up on this and posting this info. – Solomon Rutzky Jun 19 '18 at 2:35
1

Try putting the name in brackets.

DROP USER [\username];

Given the problems you are having try doing this through dynamic SQL just to confirm that you are catching every possible character. You could also try this through Object Explorer.

DECLARE @sql nvarchar(1000)
SELECT @sql = N'DROP USER [' + name + N']' FROM sys.database_principals WHERE name like '\user%'
PRINT @sql
EXEC sp_executesql @sql

@solomonrutsky brought up an interesting question. Try running the following:

SELECT s.name AS Sysusers_Name, dp.name AS DP_Name
FROM sys.sysusers s
JOIN sys.database_principals dp
    ON s.sid = dp.sid
WHERE s.name = '\user'

See if you get any results, and if so if the Sysusers_name doesn't equal the DP_Name. If you get a DP_Name try dropping that one instead.

  • Agreed. This and using the QUOTENAME() function if they need to do it procedurally. – MguerraTorres Jun 13 '18 at 17:14
  • I've tried brackets and single quotes. I receive the following error: Cannot drop the user '\username', because it does not exist or you do not have permission." – Julie Jun 13 '18 at 17:44
  • Two possibilities at this point. 1) make sure you actually have permission to drop the user (securityadmin, sysadmin, db_owner etc) 2) copy and paste the id out in case there is a hidden character. i.e. put [] and then copy and paste the name into the brackets. – Kenneth Fisher Jun 13 '18 at 18:08
  • I'm a sysadmin on the box and I've tried coping and pasting the user with the same results. – Julie Jun 13 '18 at 18:10
  • I receive a "Cannot drop the user '\user', because it does not exist or you do not have permission." error. The user does not exist in the sys.database_principals, just the sysusers table for the database. I ran the script using the sysusers table. – Julie Jun 13 '18 at 19:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.