SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Unable to remove user from db_owner role membership...why?


Unable to remove user from db_owner role membership...why?

Author
Message
dbajunior
dbajunior
SSC Veteran
SSC Veteran (209 reputation)SSC Veteran (209 reputation)SSC Veteran (209 reputation)SSC Veteran (209 reputation)SSC Veteran (209 reputation)SSC Veteran (209 reputation)SSC Veteran (209 reputation)SSC Veteran (209 reputation)

Group: General Forum Members
Points: 209 Visits: 1603
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.
John Mitchell-245523
John Mitchell-245523
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14059 Visits: 15950
I take it the name of the user isn't dbo? If not, is there a trigger somewhere that's undoing the change?

John
crazy4sql
crazy4sql
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1675 Visits: 4489
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
dbajunior
dbajunior
SSC Veteran
SSC Veteran (209 reputation)SSC Veteran (209 reputation)SSC Veteran (209 reputation)SSC Veteran (209 reputation)SSC Veteran (209 reputation)SSC Veteran (209 reputation)SSC Veteran (209 reputation)SSC Veteran (209 reputation)

Group: General Forum Members
Points: 209 Visits: 1603
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.
crazy4sql
crazy4sql
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1675 Visits: 4489

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
dbajunior
dbajunior
SSC Veteran
SSC Veteran (209 reputation)SSC Veteran (209 reputation)SSC Veteran (209 reputation)SSC Veteran (209 reputation)SSC Veteran (209 reputation)SSC Veteran (209 reputation)SSC Veteran (209 reputation)SSC Veteran (209 reputation)

Group: General Forum Members
Points: 209 Visits: 1603
Went there, but there are no users listed in the 'Users or roles:' area.
crazy4sql
crazy4sql
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1675 Visits: 4489
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
rama38udaya
rama38udaya
SSC Journeyman
SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)

Group: General Forum Members
Points: 98 Visits: 497
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
ALZDBA
ALZDBA
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12085 Visits: 8924
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


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


- How to prevent a sore throat after hours of presenting ppt ?


press F1 for solution, press shift+F1 for urgent solution :-D


Need a bit of Powershell? How about this

Who am I ? Sometimes this is me Alien but most of the time this is me Hehe
george sibbald
george sibbald
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10286 Visits: 13687
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.

---------------------------------------------------------------------
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search