cannot delete the login

Posted by Yrajudba under Sql Server on 8/22/2011 | Points: 10 | Views : 1599 | Status : [Member] | Replies : 5
HI,

I created a login under specific database i.e windows authentication and provided the admin rights as db_owner on the server.when i tried to delete the user, it says cannot delete the login, in brief the error as follows:
The database principal owns a schema and cannot be dropped.15138 sql server
can anybody help me on this, why i am not able to delete the user and...

1. how many schemas are available in sql server 2008/2005/2000.
2. Is db_datareader,db_datawriter,db_owner,db_accessadmin all these belong to schema's

thanks in advance

Thanks
Raju




Responses

Posted by: Vuyiswamb on: 8/22/2011 [Member] [MVP] [Administrator] NotApplicable | Points: 25

Up
0
Down
Take away the database ownership and delete the Database, you can right click on the database in SQl management studio and go to properties and you will see the Option "Files" change the Owner to dbo and click ok and you will be able to delete the user.


Thank you for posting at Dotnetfunda
[Administrator]

Yrajudba, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Yrajudba on: 8/22/2011 [Member] Starter | Points: 25

Up
0
Down
HI Vuyiswamb,

i followed your navigation, when i try to change the role to dbo, i received the following error:

error:
an exception occurred in SMO.
the login dbo does not exist on this server

please reply

thanks
Raju

Yrajudba, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Vuyiswamb on: 8/22/2011 [Member] [MVP] [Administrator] NotApplicable | Points: 25

Up
0
Down
This means that the user has been deleted already, refresh your SQl management studio and see if that user is still there , or just logout and login again.

Thank you for posting at Dotnetfunda
[Administrator]

Yrajudba, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: PandianS on: 8/22/2011 [Member] [MVP] Silver | Points: 25

Up
0
Down
@Yrajudba

Your user (which you trying to Drop) has ownership of someother Schema. So, You could not drop the user.
/*To identify which Schema's ownership has the <yourUser> */

SELECT name [Schema], USER_NAME(principal_id) [User]
FROM sys.schemas Where principal_id = USER_ID('<YourUserName>')
Go
/*To transfer the Ownership of the Schema to some other user Instead of yours*/

ALTER AUTHORIZATION ON SCHEMA::<Schema Name taken from the above script> TO dbo
Go
/*To identify the Schema's ownership has been transfered to dbo*/

SELECT name [Schema], USER_NAME(principal_id) [User]
FROM sys.schemas Where principal_id = USER_ID('<YourUserName>')
Go
-- The above script should not return anything...

Now you can drop the User

Cheers
www.SQLServerbuddy.blogspot.com
iLink Multitech Solutions

Yrajudba, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Yrajudba on: 8/23/2011 [Member] Starter | Points: 25

Up
0
Down
HI ,

I created windows authentication and given db_owner permissions.

so let me know what is user_name, name and principal_id stands for and what to mention in those areas.
i tried with different scenarios but i am failed to drop

please let me know

Thanks
Raju

Yrajudba, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response