Shulou(Shulou.com)06/01 Report--

This article will explain in detail the method of deleting custom database users by SQLServer. The editor thinks it is very practical, so I share it for you as a reference. I hope you can get something after reading this article.

Delete user-defined database user considerations

Users who have security objects cannot be deleted from the database. You must delete or transfer ownership of the security objects before you can delete the database users who own them.

You cannot delete the guest user, but you can disable the guest user by revoking its CONNECT permissions by executing REVOKE CONNECT FROM GUEST in any database except master or tempdb. (recommended: MySQL tutorial)

ALTER ANY USER permission on the database is required.

Delete a user-defined database user using the SSMS database management tool

1. Connect the server-"expand the database -" Select the database to delete the user-"expand the database-expand the security -" expand the user-"Select the user to be deleted-right-click -" choose to delete.

2. Click OK in the pop-up box to delete the object.

3. You do not need to refresh to view the deletion result.

Use the T-SQL script to delete a user-defined user

Grammar

Declare that the database references use database_name;go-- to determine whether there is a user-defined user, and if so, delete it. If exists (select * from sys.database_principals where name=user_name)-- modify the schema owner back to the schema itself alter authorization on schema:: [architecture _ name] to Architecture_name;-- delete members owned by the role alter role [[Architecture_name] drop member user_name;-- delete extended attribute exec sys.sp_dropextendedproperty @ name=N'tests_description', @ level0type=N'user',@level0name=N'user_name'-- delete user schema drop user user_name;go

Grammatical annotation

-- database_name

-- Database name

-- user_name

-- user name

-- Architecture_name

-- Schema name

-- tests_description

-- extended attribute name

Example

-declare that the database references use [testss]; go-- determines whether there is a user-defined user, and if so, delete it. If exists (select * from sys.database_principals where name='test1')-- modify the schema owner back to the schema itself alter authorization on schema:: [db _ accessadmin] to db_accessadmin;-- delete the member owned by the role alter role [db_accessadmin] drop member test1;-- delete the extended attribute-- exec sys.sp_dropextendedproperty @ name=N'tests_description', @ level0type=N'user',@level0name=N'test1'-- delete the user schema drop user test1;go

Example result: to delete a user using the T-SQL script, you need to refresh the user folder to view the deletion result.

After reading this article, have you learned how to delete custom database users by SQLServer? If you want to learn more skills or want to know more about it, you are welcome to follow the industry information channel. Thank you for reading.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.