0

Using SQL Server 2008. I created a new database, created a new user and mapped the user to the same login name. Gave the user all the roles available including db_owner.

The user created a new table but when the user tried to select from the table, an error "The SELECT permission was denied on the object ...." showed up.

Why doesn't the user have select permission if the user is member of the db_owner and db_datareader roles?

I recall this used to work before.

Improve this question

1 Answer 1

Reset to default
1

MOst likely the user isn't actually the DBO. Check the table name is [dbo].[tablename] and that the user actually is the dbo.

Actually - More information about the error would be nice. Cause you usually have select access to tables you have created.

Are there any deny permissions set?

Improve this answer
4
  • The default schema in the user properties is dbo. There are no deny permissions set. It's a newly created database, user and table. Under 'Securables', there are no Permissions entries.
    – Tony_Henrich
    Commented Jul 9, 2009 at 7:34
  • 1
    But what user is the database owner? having default schema = dbo, isn't the same as actually being the dbo. Right click the database and chose properties, this will tell you who is the owner. Alternatively, execute sp_helpdb 'YOURDATABASE'
    – Kev Riley
    Commented Jul 9, 2009 at 12:32
  • The new user doesn't need to be the owner. The owner is another user. He's in all the roles including db_owner and db_datareader. I don't understand why the user can create a table but not select from it. db_datareader should let the user read from all the user tables. By default dbo objects are accessible unless the permissions are explicitly denied.
    – Tony_Henrich
    Commented Jul 9, 2009 at 17:22
  • Agree the user deosn't need to be dbowner. But can you clarify exactly the db owner, the table owner and the user trying to SELECT. And don't just say they are the same, tell us exactly the usernames....
    – Kev Riley
    Commented Jul 10, 2009 at 10:43

Not the answer you're looking for? Browse other questions tagged or ask your own question.