Error 3729 Sql
Contents |
here for a quick overview of the site Help Center Detailed answers to any questions cannot drop table because it is being referenced by object you might have Meta Discuss the workings and policies of cannot drop schema 'cdc' because it is being referenced by object 'captured columns' this site About Us Learn more about Stack Overflow the company Business Learn more about hiring
Drop Schema In Sql Server
developers or posting ads with us Stack Overflow Questions Jobs Documentation Tags Users Badges Ask Question x Dismiss Join the Stack Overflow Community Stack Overflow is a
Drop Failed For Schema
community of 4.7 million programmers, just like you, helping each other. Join them; it only takes a minute: Sign up Error trying to delete database user account schema up vote 4 down vote favorite I'm trying to delete a user's schema from a database and I'm getting the following error: TITLE: Microsoft SQL sql server cannot delete user owns schema Server Management Studio ------------------------------ Drop failed for Schema 'ext_owner'. (Microsoft.SqlServer.Smo) For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.4035.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Drop+Schema&LinkId=20476 ------------------------------ ADDITIONAL INFORMATION: An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo) ------------------------------ Cannot drop schema 'ext_owner' because it is being referenced by object 'getroles'. (Microsoft SQL Server, Error: 3729) For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.4035&EvtSrc=MSSQLServer&EvtID=3729&LinkId=20476 ------------------------------ BUTTONS: OK ------------------------------ What is the getroles object? How do I get rid of the reference so I can remove the old user account? sql-server sql-server-2005 user-accounts share|improve this question asked Dec 18 '09 at 16:53 Jason 5,6701563101 What's the command that's causing this error? Also, each user has their own schema?! Surely I misunderstand. –David Lively Dec 18 '09 at 16:56 I'm trying to delete the user and to do that it asks me if I also want to delete the schema. All I am doing to get this is right click on user, click delete. –Jason Dec 18 '09
von GoogleAnmeldenAusgeblendete FelderNach Gruppen oder Nachrichten suchen
Recent PostsRecent Posts Popular TopicsPopular Topics Home Search Members Calendar Who's On Home » SQL Server 2005 » SQL Server 2005 General Discussion » How to drop a user attached to same http://www.sqlservercentral.com/Forums/Topic995919-149-1.aspx schema... How to drop a user attached to same schema with out dropping schema Rate Topic Display Mode Topic Options Author Message virgovirgo Posted Thursday, September 30, 2010 7:30 AM Valued Member Group: General Forum Members Last Login: Saturday, https://subhrosaha.wordpress.com/2010/12/17/sql-server-alterdrop-schema/ September 24, 2016 10:26 PM Points: 72, Visits: 519 As saw under schema properties below A database user can be dropped without dropping objects in a corresponding schema.My question here is when I am trying to drop a sql server user ex 'testuser' which is tagged to a schema 'testuser' which has few objects (table and function) under that schema. When I am trying to drop the user am getting a below error msg.Msg 3729, Level 16, State 1, Procedure sp_revokedbaccess, Line 51Cannot drop schema 'testuser' because it is being referenced by object 'table'So i want to know the best method to drop the existing user 'testuser' and recreate it with different set of permissions.Thanks in advance!! Post because it is #995919 Mike01Mike01 Posted Thursday, September 30, 2010 10:55 AM Ten Centuries Group: General Forum Members Last Login: Monday, September 19, 2016 6:29 PM Points: 1,265, Visits: 1,483 The user is also the owner of the schema. Change the owner of the schema, then you can drop the user. ALTER AUTHORIZATION ON SCHEMA::[Testuser] TO [dbo]GO For better, quicker answers, click on the following... http://www.sqlservercentral.com/articles/Best+Practices/61537/For better answers on performance questions, click on the following... http://www.sqlservercentral.com/articles/SQLServerCentral/66909/ Post #996079 virgovirgo Posted Friday, October 1, 2010 12:05 AM Valued Member Group: General Forum Members Last Login: Saturday, September 24, 2016 10:26 PM Points: 72, Visits: 519 Thanks Mike for the reply...but one strange thing here in my scenario i canot change the schema to dbo coz the same objects exists on dbo schema as well as testuser.So will it be better 1) to drop the schema then drop the user2) create the schema again and assign it new user created.or is there any other best way to do with out droping the schema?Cheers!! Post #996445 OliiiiOliiii Posted Friday, October 1, 2010 5:12 AM SSC Veteran Group: General Forum Members Last Login: Monday, June 8, 2015 7:12 AM Points: 206, Visits: 777 It doesn't change the schema to dbo, it changes the owner of the schema to dbo, you'll still have both your schemas and your user will be free to be droped.You can reasign the
Administration, SQL Server: Security ≈ Leave a comment TagsAlter Schema, Drop Schema, Schema, Transfer Schema are like namespace which enables us to group database objects into logical groups for ease of use, security and manageability. It is a Best Practice to use Schema Names in queries. For example, if we are trying to retrieve the data from the "Address" table from the "SalesLT" Schema, we should write the query in the following way: SELECT * FROM SalesLT.Address If a Schema has some table in it, then we cannot drop the Schema until we remove all the objects out of it. I have created a Schema "TestUser_FirstSchema" for this purpose. I am creating the sample table in the Schema. CREATE TABLE TestUser_FirstSchema.TestTable ( ID INT, FName VARCHAR(20) ) Now if I want to DROP the Schema, then I will get the following error: Msg 3729, Level 16, State 1, Line 1 Cannot drop schema ‘TestUser_FirstSchema' because it is being referenced by object ‘TestTable'. We cannot even DROP any Login or User that owns a Schema. If we try to do that then we will get the following error: Msg 15138, Level 16, State 1, Line 2 The database principal owns a schema in the database, and cannot be dropped. Now to drop a Schema, we have to first TRANSFER or DROP all the existing objects in the current Schema. The following script will transfer the "TestTable" object from the "TestUser_FirstSchema" to "TestUser_SecondSchema". ALTER SCHEMA TestUser_SecondSchema TRANSFER TestUser_FirstSchema.TestTable. So we can see that the Schema of the "TestTable" object has been changed. Now since TestUser_FirstSchema is empty now, we can go ahead and drop this Schema. DROP SCHEMA TestUser_FirstSchema Let me know if you have any doubts regarding this. Thanks, Subhro Saha Subhro's BlogGoogle Related Post navigation ← Previous post Next post &ra