SQL Server – How to Drop ‘\user’ from Database

sql serversql-server-2016

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

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

Best Answer

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.

Related Solutions

SQL Server Error 615 – db_owner Unable to Drop Database

I can guess you have the AutoClose option for database set to True. This is the default behavior when you create a database with Express Editions.

The mentioned error can occur exactly in this case. Actually the complete error message 615 states: "Could not find database ID %d, name '%.*ls'. The database may be offline. Wait a few minutes and try again." ... So it points that database could be closed during dropping.

So, go to DB properties, switch it to False and try again dropping it or use below script before dropping

ALTER DATABASE [MyDB] SET AUTO_CLOSE OFF 
GO

Many point out that is better to have AutoClose set to False. I found this article explaining a bit more about AutoClose: http://sqlmag.com/blog/worst-practice-allowing-autoclose-sql-server-databases

Small extension of the answer:

-- this works in standard SQL Server Editions, but NOT with Express Editions:
CREATE DATABASE [MyDB]
GO
DROP DATABASE [MyDB]
GO

-- this works in ALL SQL Server Editions
CREATE DATABASE [MyDB]
GO
ALTER DATABASE [MyDB] SET AUTO_CLOSE OFF 
GO
DROP DATABASE [MyDB]
GO

SQL Server – Grant Execute on xp_logevent Using sa User

How can I grant execute permissions to sa user?

You don't need to grant execute anything to sa as sa is in the sysadmin server role and can pretty much do anything. Hence, if you are getting an error executing xp_logevent, then you aren't logging in as sa. Is the connection string using context connection = true; or a regular / external connection? If using context connection = true; then the permissions are based on whoever is executing the SQLCLR stored procedure. If using a regular connection, the permissions are based on the logon account for the SQL Server NT Service (MSSQLSERVER / SQLEXPRESS if it is a default instance) by default, else it can be the permissions of whoever is executing the SQLCLR stored procedure if code has been added to do impersonation.

So, how is the SqlConnection being made? The answer to that question will help determine the appropriate fix. If this SQLCLR stored procedure is to be called by anyone (meaning, Users who are not necessarily in the db_owner database role or the sysadmin server role), then you can do the following to grant this permission to the smallest scope possible.

In the database that contains the SQLCLR stored procedure:

Create an Asymmetric Key (see CREATE ASYMMETRIC KEY)
Create a User from that Asymmetric Key (see CREATE USER)
Add this new User to the db_owner Database Role
Create a T-SQL Stored Procedure (maybe call it dbo.LogEvent) that accepts the two parameters you are passing in (@errorNumber and @message) and simply executes EXEC xp_logevent @errorNumber, @message, informational;
Grant EXECUTE on this new Stored Procedure to any Users and/or Database Roles that will be calling the SQLCLR Stored Procedure
Sign this new Stored Procedure with the same Asymmetric Key used to create the new User (see ADD SIGNATURE)
Update your SQLCLR code to execute this new T-SQL Stored Procedure (i.e. dbo.LogEvent) instead of xp_logevent
1. You can then change your using statement to have new SqlCommand("dbo.LogEvent", connection)
2. then change cmd.CommandType = CommandType.Text; to be cmd.CommandType = CommandType.StoredProcedure;
3. remove the const string executeLog line

Other notes:

Please do not use AddWithValue as there are issues with it. Instead, just create the SqlParameter with an explicitly set datatype and then add it to the SqlCommand.SqlParameters collection.
No need to call ExecuteScalar if you are not capturing something from a result set. Instead, call ExecuteNonQuery

Best Answer

Related Solutions

SQL Server Error 615 – db_owner Unable to Drop Database

SQL Server – Grant Execute on xp_logevent Using sa User

Related Question