-2

I am executing this code to create user in my database.

CREATE LOGIN john WITH PASSWORD = 'john123';
GO

USE mytestdb;
GO
CREATE USER [john] FOR LOGIN [john]
GO
ALTER USER [john] WITH DEFAULT_SCHEMA=[dbo]
GO
ALTER ROLE [db_datareader] ADD MEMBER [john]
GO
ALTER ROLE [db_datawriter] ADD MEMBER [john]
GO
GRANT EXECUTE ON SCHEMA::[dbo] TO [john]
GO

But I want to delete this my created user.

I tried to issue this command

USE myDB;
DROP USER john;
GO

but it throws this error.

The database principal owns a schema in the database, and cannot be dropped.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
jemz
  • 4,987
  • 18
  • 62
  • 102
  • [***READ THE DOCS!***](https://msdn.microsoft.com/en-us/library/ms189438.aspx) - it's all out there - you just need to **do some searching** yourself ! – marc_s Apr 15 '16 at 08:03
  • I tried this DROP USER [ IF EXISTS ] john , but it has syntax error – jemz Apr 15 '16 at 08:07
  • SQL Server 2014 (that you're using) doesn't support the `IF EXISTS` notion yet - just use `DROP USER john;` .... – marc_s Apr 15 '16 at 08:08
  • okay I remove the if exists now. but it gives me error I updated my post – jemz Apr 15 '16 at 08:10
  • #1 in Google search for "SQL Server" (and your error message above): [How to fix error 15138: ....](http://blog.sqlauthority.com/2011/12/26/sql-server-fix-error-15138-the-database-principal-owns-a-schema-in-the-database-and-cannot-be-dropped/) – marc_s Apr 15 '16 at 08:29
  • We really need a "lmgtfy" flag :D – Frederik Struck-Schøning Apr 15 '16 at 08:47

1 Answers1

1

You can see schemas owners there:

SELECT s.name AS [schema_name], dp1.name AS [owner_name]
  FROM sys.schemas AS s
  INNER JOIN sys.database_principals AS dp1 ON dp1.principal_id = s.principal_id

Then change owner:

ALTER AUTHORIZATION ON SCHEMA::[xxxx] TO [dbo]

"xxxx" - name of schema which owner is 'john' now

Ruslan K.
  • 1,912
  • 1
  • 15
  • 18