Post reply

Database role

  • September 28, 2012 at 1:09 am

    #268315

    In SQL Server Management Studio > SQL server > Databases > (My Database) > Security > Users > Right click on one the user login

    I am removing my userid as db_owner but how come I can add myself back again? Shouldn't that, I need administrator login to do that? Reason is I am removing a number of users as db_owner, and do not want them to add themselves back. It seems like I have a higher security level above this. Where can I remove this as well?

  • September 28, 2012 at 1:13 am

    #1543210

    I have also remove db_securityadmin; the only access I have is db_accessadmin, db_datareader, db_datawriter and db_ddladmin

  • September 28, 2012 at 2:05 am

    #1543237

    If you dont want yourself to re-add access, then you will want to remove db_accessadmin also.

    Members of the db_accessadmin fixed database role can add or remove access for Windows logins, Windows groups, and SQL Server logins

  • September 28, 2012 at 2:43 am

    #1543250

    that's not it. I have removed it, but still I am able to readd myself.

  • September 28, 2012 at 2:53 am

    #1543256

    Do you have any server level roles assigned to yourself?

  • September 28, 2012 at 2:53 am

    #1543257

    Also have you logged out and back in again to SQL as usually the changes dont apply until you have.

  • September 28, 2012 at 3:28 am

    #1543271

    is it because i have server roles - processadmin at the sql server > security > logins ?

  • September 28, 2012 at 3:30 am

    #1543272

    ProcessAdmin you can add a member to processadmin and also KILL connections, nothing relating to security.

  • September 28, 2012 at 3:31 am

    #1543274

    Are you a member of any group which exists on the SQL server, say a domain group or a local group which has access to other things.

  • September 28, 2012 at 9:30 pm

    #1543668

    my login is in a department login group.

    SQL server > Security > Logins > SDomain\Group_IT

    I do a right click > Properties on this group, under Server Roles tab, this group has public and processadmin assigned.

    Is this a concern?

  • October 1, 2012 at 6:21 pm

    #1544206

    Hey, thank you for the help to narrow down the issue.

    I've found it. Need to check the group access that I am in too.

    SQL Server > Security > Logins > (group logins) > Right click Properties > User Mapping > (database name) > remove db_owner

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply