Shulou(Shulou.com)05/31 Report--

Today, I will talk to you about how to delete users in MSSQL when the main body of the database, many people may not understand, in order to make you better understand, the editor summed up the following, I hope you can get something according to this article.

Delete a user of a database under ms sql2005 when prompting "the database principal has a schema in the database and cannot be deleted". 1. Check under security-> architecture to see if the user exists. If so, try deleting the user under the user. If not, run the SQL statement ALTER AUTHORIZATION ON SCHEMA::db_owner TO dbo under the following grammar.

-- and then delete it manually. The "user" solution cannot be removed because the selected user owns the object.

The code is as follows:

Use, your library name is go.

Declare tb cursor localforselect 'sp_changeobjectowner' + quotename (+ quotename (user_name (uid)) +'.'+ quotename (name),') +', 'dbo'''from sysobjects where objectproperty (id,N'isusertable') = 1and uiduser_id (' dbo') declare @ s nvarchar (4000) open tbfetch tb into @ swhile @ @ fetch_status=0beginexec (@ s) fetch tb into @ sendclose tbdeallocate tb

Third

Method 1 (Huaxia Interconnection Professional Technical Tip: because it involves changing database stored procedures, forced deletion is not recommended) the first thing you need to do is Enterprise Manager-> Select Server-> Properties-> Server Settings-> Select the system directory. -> make sure the second step is to find your database and find the sysusers table. Then your former users delete the third step and go back to the * value and return to the fourth part to rebuild the user.

Method two

Processing of selected user-owned objects that cannot be deleted in mssql

Execute the following code in the query analyzer to modify the library name

The copy code is as follows: use your library name go

Declare tb cursor localforselect 'sp_changeobjectowner' + quotename (+ quotename (user_name (uid)) +'.'+ quotename (name),') +', 'dbo'''from sysobjects where objectproperty (id,N'isusertable') = 1and uiduser_id (' dbo') declare @ s nvarchar (4000) open tbfetch tb into @ swhile @ @ fetch_status=0beginexec (@ s) fetch tb into @ sendclose tbdeallocate tb

Problems that are easy to encounter when porting MSSQL backups to another server to restore. Problems that are easy to encounter when MSSQL backups are migrated to another server to restore, especially when the database backed up from the virtual host is restored locally.

There will be a situation where the original user name and password of SQL are invalid and cannot be deleted. When a system table uses a sa connection to Select, it prompts that the table name is invalid and the user name in the original backup database cannot be deleted, and the message "the selected user cannot be removed because it owns the object." The main reason is that the original backup restores the information of the original user, resulting in isolated users.

At this point, you need to use sp_changeobjectowner to change all the relationships of the object to another user, that is, to change the owner of the database object.

Format:

Sp_changeobjectowner [@ objectname =] 'object', [@ newowner =]' owner'

Examples are entered in the query analyzer:

Sp_changeobjectowner 'web102101.tablename',' dbo' changes all the users to dbo in turn, and then deletes the orphaned users in the database, and then deletes the login information in security.

And you can create new users in turn. Here are some additions: Microsoft SQL Server error: 15138 deletion failed for the user, the database principal has a schema in the database and cannot be deleted. Workaround deletion failed for user "*". (Microsoft.SqlServer.Smo) for help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer= 9.00.1523.00&EvtSrc=Microsoft.SqlServer.Management.Smo. ExceptionTemplates.FailedOperationExceptionText&EvtID= delete + User&LinkId=20476-other information: an exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)-- the database principal has a schema in the database and cannot be deleted. (Microsoft SQL Server, error: 15138) for help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.1399&EvtSrc=MSSQLServer&EvtID=15138&LinkId=20476 solution 1 first delete the schema for this user, and then delete the corresponding user step 1. SQL SERVER MANAGEMENT STUDIO-- "database--" security-- "architecture, first delete the corresponding architecture 2. SQL SERVER MANAGEMENT STUDIO-- "database--" security-- user, delete the corresponding user solution 2-execute the following SQL statement ALTER AUTHORIZATION ON SCHEMA::db_owner TO dbo;-- and then delete it manually.

After reading the above, do you have any further understanding of how to delete the database principal when deleting users in MSSQL? If you want to know more knowledge or related content, please follow the industry information channel, thank you for your support.

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.