Post reply

Roles modification problem

  • August 25, 2003 at 10:58 pm

    #159768

    Hi All,

    In the database I have two a user called alec, who owns few tables and procedures. When I try to access his tables from sa (dbo) login, it says "invalid object name", then I checked the sa database roles and found that the db_datareader and db_datawriter was not assigned to him.

    And when I try to assign those roles, and says ok or apply, it gives me an error "Error 15405: Cannot use the reserved user or role name 'dbo'.

    I would like to know what this error means and how to rectify it?

    Thanks in advance.

    Rahul

  • August 25, 2003 at 11:01 pm

    #471355

    Rahul,

    I think as long as you have sysadm rights, you can perform anything in the database. And user "SA" can do anything in the database.

    .

  • August 26, 2003 at 8:03 am

    #471419

    mdamera's right--both the SA login and the DBO database user are system settings and (apparently) cannot be modified... and don't need to be modified. SA can do anything whatsoever within the SQL Server instance, and the DBO user can do anything within the given database, and neither can be assigned further rights or (much more importantly)limitations.

    Your original problem is probably database object ownership. You say the tables are owned by user "alec", meaning (?) that they were created by someone using that login/database user. Assuming you're in database "XXX", barring subsequent modifications SQL Server will consider these objects to be named "XXX.alec.tableName"... and then you have entered the wonderful world of database object ownership.

    Very briefly (read Books Online for more info), if your code is written along the lines of "SELECT ... from tableName", you'll only access this table if the current database user is alec; everyone else (including SA) will give you an error because they're looking for XXX.dbo.tableName (or XXX.<myLogin>.tableName). Code it like "SELECT ... from alec.tableName", and then every user attempt to access that table--and then you have to set up proper access rights.

    Most (virtually all??) people avoid this mess just set all objects outside of the development environment to be owned by dbo. Again, please read BOL if you need to know about this, as it takes pages and pages to explain all of this in detail. A possible quick shortcut to your research, read up on "sp_changeobjectowner", as this it what you'd use to change an object's owner.

    Philip

  • August 26, 2003 at 8:06 am

    #471422

    And it turns out they're discussing another aspect of database object ownership over in discussion thread http://qa.sqlservercentral.com/forum/link.asp?TOPIC_ID=15602

    Philip

Viewing 4 posts - 1 through 3 (of 3 total)

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