can not drop user from database

I can not delete user from a database in sql2005 beta 3.
the message errror is :

TITLE: SQL Server Management Studio
-

Drop failed for User 'Amministratore'. (Microsoft.SqlServer.Smo)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft SQL Server&ProdVer=9.00.0981.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Drop+User&LinkId=20476

-
ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

-

The database principal owns a schema and cannot be dropped. (Microsoft SQL Server, Error: 15138)

in sql 2000 I can delete the user very easy, but in sql 2005 I don't understant How to do it.

In SQL Server 2005, schemas are real entities. You cannot drop a user that owns schemas; you first have to either drop the schemas or change their owner to be another user.

Thanks
Laurentiu

|||

The previous answer is helpful. I am just elaborating for newbies in SQL 2005.

1) Expand Schemas(should be like a folder under <yourdatabase> -> Security) .
2) Delete the unwanted "userSchema".
3) Then, go back to the User(a folder like thing) and delete it.

|||

Hi,

I am having the same problem. When i went to delete the schema..i got the error message "drop failed for schema"

Cannot drop schema 'wch1' because it is being referenced by object 'Alert_List'. (.Net SqlClient Data Provider)

any suggestions?

|||

Before dropping a schema, it must be empty. Looks like in your case, you still have an object in the schema: Alert_List. You may either choose to drop this object first or you may choose to move it to another schema (using ALTER SCHEMA TRANSFER). When the schema will be empty, you will be able to drop it.

Thanks
Laurentiu

|||Is there any way i can tell what objects my schema has?|||

You can query the catalogs. For example, you can execute the following query:

select * from sys.objects where schema_id = schema_id('s')

to find out the objects that reside in schema 's'.

Thanks
Laurentiu

|||

I also had this problem.I was not able to find out which Schema that the login owned.I do not know of a stored proc function that will list ownership of schemas given an owner.Maybe someone can add that to this thread.

I was able to see which schemas were owned by the login by viewing the properties of each schema and seeing who what listed as the owner.

Example:

I opened the properties window of schema db_datareader and notices that the owner was User1.I changed the owner to be db_datareader and then was able to drop user1.

Regards,

DataSort

|||

Schemas are owned by users, not by logins. In SQL Server, logins and users are not the same thing.

To find out the schemas owned by a user, you can run the following query:

select * from sys.schemas where principal_id = user_id('user_name')

Thanks
Laurentiu

|||Thank you!

can not drop user from database

I can not delete user from a database in sql2005 beta 3.
the message errror is :

TITLE: SQL Server Management Studio
-

Drop failed for User 'Amministratore'. (Microsoft.SqlServer.Smo)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft SQL Server&ProdVer=9.00.0981.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Drop+User&LinkId=20476

-
ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

-

The database principal owns a schema and cannot be dropped. (Microsoft SQL Server, Error: 15138)

in sql 2000 I can delete the user very easy, but in sql 2005 I don't understant How to do it.

In SQL Server 2005, schemas are real entities. You cannot drop a user that owns schemas; you first have to either drop the schemas or change their owner to be another user.

Thanks
Laurentiu

|||

The previous answer is helpful. I am just elaborating for newbies in SQL 2005.

1) Expand Schemas(should be like a folder under <yourdatabase> -> Security) .
2) Delete the unwanted "userSchema".
3) Then, go back to the User(a folder like thing) and delete it.

|||

Hi,

I am having the same problem. When i went to delete the schema..i got the error message "drop failed for schema"

Cannot drop schema 'wch1' because it is being referenced by object 'Alert_List'. (.Net SqlClient Data Provider)

any suggestions?

|||

Before dropping a schema, it must be empty. Looks like in your case, you still have an object in the schema: Alert_List. You may either choose to drop this object first or you may choose to move it to another schema (using ALTER SCHEMA TRANSFER). When the schema will be empty, you will be able to drop it.

Thanks
Laurentiu

|||Is there any way i can tell what objects my schema has?|||

You can query the catalogs. For example, you can execute the following query:

select * from sys.objects where schema_id = schema_id('s')

to find out the objects that reside in schema 's'.

Thanks
Laurentiu

|||

I also had this problem.I was not able to find out which Schema that the login owned.I do not know of a stored proc function that will list ownership of schemas given an owner.Maybe someone can add that to this thread.

I was able to see which schemas were owned by the login by viewing the properties of each schema and seeing who what listed as the owner.

Example:

I opened the properties window of schema db_datareader and notices that the owner was User1.I changed the owner to be db_datareader and then was able to drop user1.

Regards,

DataSort

|||

Schemas are owned by users, not by logins. In SQL Server, logins and users are not the same thing.

To find out the schemas owned by a user, you can run the following query:

select * from sys.schemas where principal_id = user_id('user_name')

Thanks
Laurentiu

|||Thank you!

can not drop user from database

I can not delete user from a database in sql2005 beta 3.
the message errror is :

TITLE: SQL Server Management Studio
-

Drop failed for User 'Amministratore'. (Microsoft.SqlServer.Smo)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft SQL Server&ProdVer=9.00.0981.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Drop+User&LinkId=20476

-
ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

-

The database principal owns a schema and cannot be dropped. (Microsoft SQL Server, Error: 15138)

in sql 2000 I can delete the user very easy, but in sql 2005 I don't understant How to do it.

In SQL Server 2005, schemas are real entities. You cannot drop a user that owns schemas; you first have to either drop the schemas or change their owner to be another user.

Thanks
Laurentiu

|||

The previous answer is helpful. I am just elaborating for newbies in SQL 2005.

1) Expand Schemas(should be like a folder under <yourdatabase> -> Security) .
2) Delete the unwanted "userSchema".
3) Then, go back to the User(a folder like thing) and delete it.

|||

Hi,

I am having the same problem. When i went to delete the schema..i got the error message "drop failed for schema"

Cannot drop schema 'wch1' because it is being referenced by object 'Alert_List'. (.Net SqlClient Data Provider)

any suggestions?

|||

Before dropping a schema, it must be empty. Looks like in your case, you still have an object in the schema: Alert_List. You may either choose to drop this object first or you may choose to move it to another schema (using ALTER SCHEMA TRANSFER). When the schema will be empty, you will be able to drop it.

Thanks
Laurentiu

|||Is there any way i can tell what objects my schema has?|||

You can query the catalogs. For example, you can execute the following query:

select * from sys.objects where schema_id = schema_id('s')

to find out the objects that reside in schema 's'.

Thanks
Laurentiu

|||

I also had this problem.I was not able to find out which Schema that the login owned.I do not know of a stored proc function that will list ownership of schemas given an owner.Maybe someone can add that to this thread.

I was able to see which schemas were owned by the login by viewing the properties of each schema and seeing who what listed as the owner.

Example:

I opened the properties window of schema db_datareader and notices that the owner was User1.I changed the owner to be db_datareader and then was able to drop user1.

Regards,

DataSort

|||

Schemas are owned by users, not by logins. In SQL Server, logins and users are not the same thing.

To find out the schemas owned by a user, you can run the following query:

select * from sys.schemas where principal_id = user_id('user_name')

Thanks
Laurentiu

|||Thank you!

can not drop user from database

I can not delete user from a database in sql2005 beta 3.
the message errror is :

TITLE: SQL Server Management Studio
-

Drop failed for User 'Amministratore'. (Microsoft.SqlServer.Smo)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft SQL Server&ProdVer=9.00.0981.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Drop+User&LinkId=20476

-
ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

-

The database principal owns a schema and cannot be dropped. (Microsoft SQL Server, Error: 15138)

in sql 2000 I can delete the user very easy, but in sql 2005 I don't understant How to do it.

In SQL Server 2005, schemas are real entities. You cannot drop a user that owns schemas; you first have to either drop the schemas or change their owner to be another user.

Thanks
Laurentiu

|||

The previous answer is helpful. I am just elaborating for newbies in SQL 2005.

1) Expand Schemas(should be like a folder under <yourdatabase> -> Security) .
2) Delete the unwanted "userSchema".
3) Then, go back to the User(a folder like thing) and delete it.

|||

Hi,

I am having the same problem. When i went to delete the schema..i got the error message "drop failed for schema"

Cannot drop schema 'wch1' because it is being referenced by object 'Alert_List'. (.Net SqlClient Data Provider)

any suggestions?

|||

Before dropping a schema, it must be empty. Looks like in your case, you still have an object in the schema: Alert_List. You may either choose to drop this object first or you may choose to move it to another schema (using ALTER SCHEMA TRANSFER). When the schema will be empty, you will be able to drop it.

Thanks
Laurentiu

|||Is there any way i can tell what objects my schema has?|||

You can query the catalogs. For example, you can execute the following query:

select * from sys.objects where schema_id = schema_id('s')

to find out the objects that reside in schema 's'.

Thanks
Laurentiu

|||

I also had this problem.I was not able to find out which Schema that the login owned.I do not know of a stored proc function that will list ownership of schemas given an owner.Maybe someone can add that to this thread.

I was able to see which schemas were owned by the login by viewing the properties of each schema and seeing who what listed as the owner.

Example:

I opened the properties window of schema db_datareader and notices that the owner was User1.I changed the owner to be db_datareader and then was able to drop user1.

Regards,

DataSort

|||

Schemas are owned by users, not by logins. In SQL Server, logins and users are not the same thing.

To find out the schemas owned by a user, you can run the following query:

select*fromsys.schemaswhere principal_id =user_id('user_name')

Thanks
Laurentiu

|||Thank you!

can not drop user from database

I can not delete user from a database in sql2005 beta 3.
the message errror is :

TITLE: SQL Server Management Studio
-

Drop failed for User 'Amministratore'. (Microsoft.SqlServer.Smo)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft SQL Server&ProdVer=9.00.0981.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Drop+User&LinkId=20476

-
ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

-

The database principal owns a schema and cannot be dropped. (Microsoft SQL Server, Error: 15138)

in sql 2000 I can delete the user very easy, but in sql 2005 I don't understant How to do it.

In SQL Server 2005, schemas are real entities. You cannot drop a user that owns schemas; you first have to either drop the schemas or change their owner to be another user.

Thanks
Laurentiu

|||

The previous answer is helpful. I am just elaborating for newbies in SQL 2005.

1) Expand Schemas(should be like a folder under <yourdatabase> -> Security) .
2) Delete the unwanted "userSchema".
3) Then, go back to the User(a folder like thing) and delete it.

|||

Hi,

I am having the same problem. When i went to delete the schema..i got the error message "drop failed for schema"

Cannot drop schema 'wch1' because it is being referenced by object 'Alert_List'. (.Net SqlClient Data Provider)

any suggestions?

|||

Before dropping a schema, it must be empty. Looks like in your case, you still have an object in the schema: Alert_List. You may either choose to drop this object first or you may choose to move it to another schema (using ALTER SCHEMA TRANSFER). When the schema will be empty, you will be able to drop it.

Thanks
Laurentiu

|||Is there any way i can tell what objects my schema has?|||

You can query the catalogs. For example, you can execute the following query:

select * from sys.objects where schema_id = schema_id('s')

to find out the objects that reside in schema 's'.

Thanks
Laurentiu

|||

I also had this problem.I was not able to find out which Schema that the login owned.I do not know of a stored proc function that will list ownership of schemas given an owner.Maybe someone can add that to this thread.

I was able to see which schemas were owned by the login by viewing the properties of each schema and seeing who what listed as the owner.

Example:

I opened the properties window of schema db_datareader and notices that the owner was User1.I changed the owner to be db_datareader and then was able to drop user1.

Regards,

DataSort

|||

Schemas are owned by users, not by logins. In SQL Server, logins and users are not the same thing.

To find out the schemas owned by a user, you can run the following query:

select * from sys.schemas where principal_id = user_id('user_name')

Thanks
Laurentiu

|||Thank you!
sql

can not drop user from database

I'm getting the can't drop user error. Is there a way to change the schema owner via smo? I've tried the following code to change the schema owner back to a different user. I don't get an error or exception but the schema owner doesn't change.

Database database = new Server("my server").Databases["my Database"];

database.Schemas["db_owner"].Owner = "db_owner";

Never mind, the following worked.

Database database = new Server("my server").Databases["my Database"];

database.Schemas["db_owner"].Owner = "db_owner";

database.Schemas["db_owner"].Alter();

|||

John, giving that your question is SMO related, I split it from the thread you posted it in and I moved it to the SMO forum.

Thanks
Laurentiu

Can not drop merge replication.

Hi, i have a publisher running sql server 2000 sp3. I have
configured a merge replication with two push
subscriptions. I need to delete the subscriptions and the
publication but when i try to do it, i get this error:
Error 207: invalid column name 'enabled_for_syncmgr'
I'll really appreciate your help because i need to remove
replication from this database.
Regards,
Maria,
try running sp_helpsubscription @.publication = 'publication' , @.article =
'article'.
For each one found, run sp_dropmergesubscription, then drop the publication.
If it errors, please state at which point, and any relevant data from
sp_helpsubscription.
Regards,
Paul Ibison
|||Thanks a lot for your help. Yesterday i tryed to disable
this database for merge publications and it displays the
same error.
After that I execute the procedure you recommend me. Today
I found that the database is not configured for
replication but i still see the publication and
subscriptions in the replication monitor. The snapshot and
the subscriptions are failed displaying:
"The 'databasename' database is not published for merge
replication. "
And the subscribers displays:
"Publication 'publicationname' does not exists. The step
fails."
If i try to delete the publication diplays this error:
"Error: 21776. [SQL-DMO]The name 'publicationname' was not
found in the mergepublications collation..."
What should I do to delete the publication and subscribers
from the replication monitor?
Thanks a lot for your help.

>--Original Message--
>Maria,
>try running sp_helpsubscription @.publication
= 'publication' , @.article =
>'article'.
>For each one found, run sp_dropmergesubscription, then
drop the publication.
>If it errors, please state at which point, and any
relevant data from
>sp_helpsubscription.
>Regards,
>Paul Ibison
>
>.
>
|||Maria,
if you're lucky, this database is not needed to be used for replication as a publisher or subscriber. In this case you can run
sp_removedbreplication 'dbname'.
If not, then I'd script out what you need, remove what you can using hte replication interface in EM, then run the script above because there are some corrupted system table records which are better removed. Subsequently you can readd your scripts. Depend
ing on your replication you may be able to resume replication without reinitializing the data. Anyway, hopefully this is academic and you can simply run the command above on its own.
HTH,
Paul Ibison

Can not drop a member from a role

I've just changed server from SQL2000 to SQL2005 (by backup/restore).
But I can not drop a user from a role. I did it from Management Studio.
It didn't give any error message but even I refresh the db, I can still
see same user in the role.
Then I've tried sp_droprolemember 'X','Y' but same result. Does not
give any error message, looks like it did it. But user is still in the
role.
Even I'm server admin, I can not do it. I'll be happy if someone can
help me to solve the issue.
Thx
AvniCheck out sp_change_users_login. Run it to find the mismatched users, as
well as to correct the problem. Then, drop the role member.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
<avni.cengel@.gmail.com> wrote in message
news:1141984205.157637.62570@.u72g2000cwu.googlegroups.com...
I've just changed server from SQL2000 to SQL2005 (by backup/restore).
But I can not drop a user from a role. I did it from Management Studio.
It didn't give any error message but even I refresh the db, I can still
see same user in the role.
Then I've tried sp_droprolemember 'X','Y' but same result. Does not
give any error message, looks like it did it. But user is still in the
role.
Even I'm server admin, I can not do it. I'll be happy if someone can
help me to solve the issue.
Thx
Avni

Can not drop a member from a role

I've just changed server from SQL2000 to SQL2005 (by backup/restore).
But I can not drop a user from a role. I did it from Management Studio.
It didn't give any error message but even I refresh the db, I can still
see same user in the role.
Then I've tried sp_droprolemember 'X','Y' but same result. Does not
give any error message, looks like it did it. But user is still in the
role.
Even I'm server admin, I can not do it. I'll be happy if someone can
help me to solve the issue.
Thx
AvniCheck out sp_change_users_login. Run it to find the mismatched users, as
well as to correct the problem. Then, drop the role member.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
<avni.cengel@.gmail.com> wrote in message
news:1141984205.157637.62570@.u72g2000cwu.googlegroups.com...
I've just changed server from SQL2000 to SQL2005 (by backup/restore).
But I can not drop a user from a role. I did it from Management Studio.
It didn't give any error message but even I refresh the db, I can still
see same user in the role.
Then I've tried sp_droprolemember 'X','Y' but same result. Does not
give any error message, looks like it did it. But user is still in the
role.
Even I'm server admin, I can not do it. I'll be happy if someone can
help me to solve the issue.
Thx
Avni

Can not drop a member from a role

I've just changed server from SQL2000 to SQL2005 (by backup/restore).
But I can not drop a user from a role. I did it from Management Studio.
It didn't give any error message but even I refresh the db, I can still
see same user in the role.
Then I've tried sp_droprolemember 'X','Y' but same result. Does not
give any error message, looks like it did it. But user is still in the
role.
Even I'm server admin, I can not do it. I'll be happy if someone can
help me to solve the issue.
Thx
Avni
Check out sp_change_users_login. Run it to find the mismatched users, as
well as to correct the problem. Then, drop the role member.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
<avni.cengel@.gmail.com> wrote in message
news:1141984205.157637.62570@.u72g2000cwu.googlegro ups.com...
I've just changed server from SQL2000 to SQL2005 (by backup/restore).
But I can not drop a user from a role. I did it from Management Studio.
It didn't give any error message but even I refresh the db, I can still
see same user in the role.
Then I've tried sp_droprolemember 'X','Y' but same result. Does not
give any error message, looks like it did it. But user is still in the
role.
Even I'm server admin, I can not do it. I'll be happy if someone can
help me to solve the issue.
Thx
Avni
sql

Can I run more than one MDX statement separated with ";"

I would like to run more than one MDX statement in a RS2005 Report. I want to create a subcube and generate reports and then drop the subcube all from with RS2005.The SSAS provider doesn't support multiple statements as explained in this thread.