Post reply

changing login privileges

  • January 1, 2007 at 11:00 pm

    #90872

    Hi,

    I have an login called "wombat" which is a "system administrator" server role, and I want to change it to have restricted access to a particular database (making it a database owner), but I get the following error message:

    error 21002: [SQL-DMO] User 'wombat' already exists

    I am changing this through Enterprise Manager.

    Can anyone help?  Is there another way to achieve this?

    thanks.

     

  • January 1, 2007 at 11:20 pm

    #680693

    Is this database restore from some other server?

    Might be SID missmatch problem.

    check the sysusers table in that database, if the row exists you can delete the row and set the dbo access in EM.

    or You can use sp_change_users_login to fix this.  See the Books Online for more details.

    MohammedU
    Microsoft SQL Server MVP

  • January 1, 2007 at 11:54 pm

    #680696

    no its not a restore from another database - does this make a difference?

  • January 2, 2007 at 9:10 am

    #680767

    select * from ..sysusers. See if it's there

  • January 2, 2007 at 3:05 pm

    #680848

    ok thanks for your help.....I just have one more question....

    Why doesn't this work through EM?  Is it a bug?  Or was it never meant to work through EM?  This is a new database - not a restored one.

    Thanks again

     

  • January 2, 2007 at 4:41 pm

    #680874

    You didn't answer the question..

    Is that user exists in sysusers table?

    There may the another user in that db with different SID and your login SID is not matching it... so you need to drop the user from the db and grant the access to the user it will create new user in the db...

    It is the same if you use QA or EM...

    MohammedU
    Microsoft SQL Server MVP

  • January 2, 2007 at 5:48 pm

    #680878

    ok I'll answer your question.....there is one row.

    thanks for all your help - I'll figure it out from here.

    I really just wanted to know why it didn't work through EM thats all, but I guess I need to delete the row first, then use EM.  Or alternatively, as you have suggested, use sp_change_users_login....

    All the best and happy new year!

     

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

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