September 15, 2011 at 8:20 am
I am fairly new to this...
Trying to remove a user from the db_owner role membership on a particular database.
I uncheck db_owner, then click OK. When I review the change, db_owner is checked again!
What am I missing here? Is there some other change I have to do before this one?
Thanks in advance for your help.
September 15, 2011 at 8:29 am
I take it the name of the user isn't dbo? If not, is there a trigger somewhere that's undoing the change?
John
September 15, 2011 at 8:34 am
go to database--security--schemas--db_owner.
remove the user from here. It should work.
If it works then please share your servicepack level of 2005(as you raised it in 2005 forum so assuming your database is in 2005)
----------
Ashish
September 15, 2011 at 8:46 am
I am sorry as I posted this question in the wrong forum. We are actually using 2008. Does 2008 require a different solution other than what is recommended above? I went to db/security/schemas/db_owner and the owner shown is db_owner.
September 15, 2011 at 8:49 am
Does 2008 require a different solution other than what is recommended above? I went to db/security/schemas/db_owner and the owner shown is db_owner.
go to permission tab and if user is listed there, remove all permission.
----------
Ashish
September 15, 2011 at 8:57 am
Went there, but there are no users listed in the 'Users or roles:' area.
September 15, 2011 at 9:02 am
ok, next try is:-
database--security--roles--database roles--db_owner--properties--generaltab.
Is your user listed here in 'role members'? if yes,remove it.
----------
Ashish
September 18, 2011 at 2:54 am
Hi,
It is always good practice before revoking DBO role,identify what user has the permission on database level &it's Objects.
Once in Handy ->You can perform through the GUI i,e if that user has any dependency permission -then remove first then go with the revoking DBO
or
best way use the T-SQL commands.
Note ->Just for information only-
Identify that what are all the Jobs(applications/Maintenance Plan jobs) are depends on those Id's? Because you are going to revoke High Privilege role.
If you are Ok to revoke role then you can ignore.
Regards,
Rama Udaya.K
September 18, 2011 at 3:16 am
Keep in mind if that use is member of a user defined db role that has been added to the db_owner groupmembers, SSMS will show db_owner membership as well !
In such case, just unticking DB_Owner will not have the intended effect.
You'll need to just remove that user from the user defined db role that is member of the db_owner group.
Just worth the double check.
You can use this to see how a user gets to the db.
EXEC master..xp_logininfo @acctname = 'domain\user',@option = 'all' -- show all paths an account is allowed into the database
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
September 18, 2011 at 2:44 pm
dbajunior (9/15/2011)
Went there, but there are no users listed in the 'Users or roles:' area.
from this it sounds as if the user is dbo of the database (not quite the same thins as being in the db_owner role, but it will have the same rights plus the ability to add other users to the db_owner role).
To remove this user you need to set another login as dbo of the database, use command alter authorization or exec sp_changedbowner for this.
running sp_helpuser you should see dbo mapped to the login.
---------------------------------------------------------------------
September 21, 2011 at 2:20 pm
George, in the original post dbajunior said "I uncheck db_owner, then click OK." Well in my version of SQL Server 2008, the login mapped to dbo is not listed in the users list of the databases except as dbo, you can not map a login that is already in the database as another user as dbo, and the in the user mapping area of the logins properties, trying to remove the check for db_owner generates an exception 'cannot use special principal dbo' so he would never be able to successfully click "OK".
dbajunior; In your original attempt were you looking at the user as listed in the security\users folder of the database, or the user mapping page of the login properties under the server's security\logins folder?
Also just for fun and giggles why don't you run "sp_Changedbowner 'sa' " in that databae. Then try see if your user in listed in the security\users folder of the database. If it is not then it was mapped to the "dbo" user. If you see it there then that was not the issue.
September 22, 2011 at 3:59 am
ejoell 66477 (9/21/2011)
George, in the original post dbajunior said "I uncheck db_owner, then click OK." Well in my version of SQL Server 2008, the login mapped to dbo is not listed in the users list of the databases except as dbo, you can not map a login that is already in the database as another user as dbo, and the in the user mapping area of the logins properties, trying to remove the check for db_owner generates an exception 'cannot use special principal dbo' so he would never be able to successfully click "OK".
it can happen if the user is defined in the database already and then that user creates the database by attach or restore, user will also become dbo.
---------------------------------------------------------------------
April 10, 2013 at 2:43 pm
I think this should work.
sp_change_users_login 'dbo', 'dbo', 'Update_one'
The issue at least to me - was my dbo was mapped to the wrong user. And that is a no-go.
You will get this error:
"Terminating this procedure. 'dbo' is a forbidden value for the login name parameter in this procedure."
In my case, I checked the user properties, and the bad map was gone. I was able to continue after this fix and drop the login.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply