User or role 'XXX' does not exist in this database.

Discussion in 'Databases' started by rdmartin, Jan 14, 2009.

Thread Status:
Threads that have been inactive for 5 years or longer are closed to further replies. Please start a new thread.
  1. Why is it that one cannot add the main SQL login to any roles in SQL Server 2008? For example, say I create a role named MyRole and then try to add the user to it:

    EXEC sp_addrolemember @rolename=N'MyRole', @membername=N'SQL2008_XXXXXX_user'

    It will fail with 'User or role 'SQL2008_XXXXXX_user' does not exist in this database.'

    I understand that the login is already a db_owner and therefore doesn't even need to be in this role, but I am the developer for a widely distributed application (Gallery Server Pro) and my install script makes the assumption that sp_addrolemember works for any database login other than 'sa'. This assumption fails on your hosted SQL 2008 service.

    My guess is this is related to my observation that the user *does not* appear in the Users node of Management Studio.

    How are you achieving this effect? This is not how your SQL 2005 service works. I am trying to understand the underlying principle so I can code an effective bug fix in my installation script.

    Thanks,
    Roger Martin
    Lead Developer and Creator of Gallery Server Pro
    www.galleryserverpro.com
     
  2. I think I got to the bottom of this. I believe DiscountASP.NET is assigning the main SQL login as the owner of the database. When I experimented on a database in my local instance of SQL Server 2008, I received the behavior I described in the original post: The login does not appear when you look at the list of database logins in Management Studio. And you can't add the user to roles using sp_addrolemember.

    I think the fix is going to be catching the exception and then, within the catch clause, execute SELECT IS_MEMBER ('db_owner') to see if the current user is a member of the db_owner role. If yes, then it is OK that running sp_addrolemember failed. If not, rethrow the exception.

    Interestingly, even though IS_MEMBER returns true, the user does not appear in the list of db_owner members in Management Studio. I think this has something to do with the owner being aliased as 'dbo', which *does* appear, but I don't really understand it.

    Cheers,
    Roger Martin
     
  3. JorgeR

    JorgeR DiscountASP.NET Staff

    The way the sql 2008 server is setup is to accommodate the way the SSMS08 and the SQL engine is designed. After working with Microsoft and trying to find the best solution to accommodate our customers, the SQL login is actually the database owner as mentioned by Roger in his post. This been said, this helps our customers login from an SSMS client to see their own database and not list all database like in our SQL 2005 service.


    junior

    DiscountASP.NET

    www.DiscountASP.NET
     
Thread Status:
Threads that have been inactive for 5 years or longer are closed to further replies. Please start a new thread.

Share This Page