Post reply

Altering the DB Role?

  • September 19, 2014 at 6:31 am

    #311637

    Hi All,

    I have created a login with some restrictions ,By default the Login has DB_Owner Role,

    Now i want to change that Role to DB_datareader.

    How can i alter the Login role from db_owner to db_datareader

    Regards
    Chowdary...

  • September 19, 2014 at 6:41 am

    #1746692

    You don't alter the role. Users can have many roles. Remove that user from the db_owner role, then add him to the roles you want him to have.

    sp_adddbrole and sp_removedbbrole I think the two procs are.

    Also, logins do not get db_owner by default. By default they get no roles at all.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • September 19, 2014 at 7:11 am

    #1746705

    Thank you for your Reply @Gail,

    I am trying to remove the db_owner role to that Login but it is showing an error like

    "Cannot Use the Special Principal 'dbo' ",

    I cannot able to remove and also not assign the other db role to him same error am getting......

    Regards
    Chowdary...

  • September 19, 2014 at 7:32 am

    #1746714

    What exactly are you doing?

    Those aren't roles applied to logins. They are database roles, they are applied to the database user.

    Is the login a member of the sysadmin fixed server role? Is that login the owner of the database?

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • September 19, 2014 at 7:40 am

    #1746716

    Chowdary's (9/19/2014)

    Thank you for your Reply @Gail,

    I am trying to remove the db_owner role to that Login but it is showing an error like

    "Cannot Use the Special Principal 'dbo' ",

    I cannot able to remove and also not assign the other db role to him same error am getting......

    The user owns the database, in this scenario the user maps in via the dbo database user.

    Post the results of the following query

    SELECT SUSER_SNAME(owner_sid) AS DatabaseOwner

    , name AS DatabaseName

    FROM sys.databases

    WHERE name = 'yourdb'

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

    "Ya can't make an omelette without breaking just a few eggs" 😉

  • September 21, 2014 at 10:54 pm

    #1746937

    SELECT SUSER_SNAME(owner_sid) AS DatabaseOwner, name AS DatabaseName

    FROM sys.databases

    WHERE name = 'yourdb' code]

    Hi Perry Whittle..

    Here Login Name is :Restricted,

    Db Name is :Maintain

    PFA....

    Regards
    Chowdary...

  • September 22, 2014 at 12:34 am

    #1746938

    So the login "restricted" owns the database "maintain", is this the login you wish to change database access for?

    Under sql server 2012, to add a database principal to a role you alter the role as follows

    ALTER ROLE somerole ADD MEMBER somedbuser

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

    "Ya can't make an omelette without breaking just a few eggs" 😉

Viewing 7 posts - 1 through 6 (of 6 total)

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