Default owner when reference a table that is not yours!

[acarruth]

Hi,

Pretty new to MS SQL server, and I'm trying to have some semblance of security.. I just want a user that can select,insert,update and delete, but not create tables etc. I've been able to do this, but only by referencing tables in the manner

dbowner.tablename

Is there a way to reference another person's table without having to prefix it with dbowner each time? (Currently, the heaps and heaps of code don't have the dbowner prefix in the queries, so ideally I don't want to have to change everything! )

Any help would be appreciated..

Thanks,

Allan

 

[JamesLean]

If you don't specify an owner then SQL Server will look for the table name under the current user first then under dbo. You can't specify another user for it to search automatically - you have to specify the owner in the select.

As a general rule, it is a good idea in production dbs to have everything owned by dbo. This avoids problems with ownership like this.

--James