Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Unable to remove user from db_owner role membership...why? Expand / Collapse
Author
Message
Posted Thursday, September 15, 2011 8:20 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 3:28 AM
Points: 119, Visits: 1,486
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.
Post #1175749
Posted Thursday, September 15, 2011 8:29 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 7:10 AM
Points: 5,386, Visits: 9,967
I take it the name of the user isn't dbo? If not, is there a trigger somewhere that's undoing the change?

John
Post #1175753
Posted Thursday, September 15, 2011 8:34 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 4:22 AM
Points: 882, Visits: 4,107
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
Post #1175759
Posted Thursday, September 15, 2011 8:46 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 3:28 AM
Points: 119, Visits: 1,486
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.
Post #1175775
Posted Thursday, September 15, 2011 8:49 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 4:22 AM
Points: 882, Visits: 4,107

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
Post #1175786
Posted Thursday, September 15, 2011 8:57 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 3:28 AM
Points: 119, Visits: 1,486
Went there, but there are no users listed in the 'Users or roles:' area.
Post #1175795
Posted Thursday, September 15, 2011 9:02 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 4:22 AM
Points: 882, Visits: 4,107
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
Post #1175801
Posted Sunday, September 18, 2011 2:54 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: 2 days ago @ 3:00 AM
Points: 50, Visits: 457
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
Post #1176958
Posted Sunday, September 18, 2011 3:16 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Friday, September 26, 2014 7:44 AM
Points: 6,731, Visits: 8,476
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


Don't drive faster than your guardian angel can fly ...
but keeping both feet on the ground won't get you anywhere

- 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"


Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me
Post #1176961
Posted Sunday, September 18, 2011 2:44 PM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 1:22 PM
Points: 5,872, Visits: 12,978
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.


---------------------------------------------------------------------

Post #1177041
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse