Answered by:
Not able to revoke db_owner permission

Question
Answers
-
To remove a user from a role on SQL 2008 and earlier:
EXEC sp_droprolemember 'db_owner', 'thisuser'
On SQL 2012 and later:
ALTER ROLE db_owner DROP MEMBER thisuser
If you insist of using the UI, you will need to figure what commands etc it uses.
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se- Marked as answer by tracycaiMicrosoft contingent staff, Moderator Monday, May 19, 2014 9:49 AM
All replies
-
Check if the user is not Orphan user by below command
exec sp_change_users_login 'Report'
If this is orphan the first this and then revoke.
USE <database_name>;
GO
sp_change_users_login @Action='update_one', @UserNamePattern='<database_user>', @LoginName='<login_name>';
GO
Please click "Propose As Answer" if a post solves your problem, or "Vote As Helpful" if a post has been useful to you- Edited by Pradeep_DBA Saturday, May 10, 2014 3:49 AM
-
-
If I execute the below qeury
exec
sp_change_users_login'Report'
Nothing is showing in output.
Shashikala
Make sure you run this query in DATABASE in which you are trying to find orphaned userPlease mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers
My TechNet Wiki Articles -
To remove a user from a role on SQL 2008 and earlier:
EXEC sp_droprolemember 'db_owner', 'thisuser'
On SQL 2012 and later:
ALTER ROLE db_owner DROP MEMBER thisuser
If you insist of using the UI, you will need to figure what commands etc it uses.
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se- Marked as answer by tracycaiMicrosoft contingent staff, Moderator Monday, May 19, 2014 9:49 AM