adsense

Wednesday, August 9, 2017

drop database user that owns a schema


First you will have to determine the schema's owned by a given user in order to  drop the user.

Execute the following query to determine the schemas owned by the user  named ‘user1’ .

SELECT name FROM sys.schemas
WHERE principal_id = USER_ID('user1');

When you run the query above, suppose you get db_datawriter as the schema owned by user (in this case user1)

Next step is to change the owner of the schema db_datawriter to some other user (e.g. dbo):

ALTER AUTHORIZATION ON SCHEMA::db_datawriter TO dbo;



You will have to remove all the schemas to drop a db user. When the user has no schemas owned you can drop it.


No comments:

Post a Comment