Object owner question

Question

Post reply

Object owner question

as_1234

SSCrazy

Points: 2881
More actions
August 26, 2025 at 12:15 pm

#4640949

Hello,
Currently all of our tables e.t.c are owned by dbo.
In one of our databases I need to move the tables to a different owner to prevent an ownership chain.
What I'm a bit unsure about is the best type of account to use for the table owner. I think 'user without login' is perhaps the best option as there isn't a password to be managed and no one can login into the account without impersonation rights.
I would appreciate any experience/advice anyone has?
If it's relevant we are upgrading to SQL 2022 soon.
Thanks

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

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

Phil Parkin SSC Guru Points: 247180 More actions · Answer 1

Can you go into a bit more detail regarding this 'ownership chain' you are trying to prevent?

as_1234 SSCrazy Points: 2881 More actions · Answer 2

Thanks for your reply.

We restore a database from one server to another. On the destination database users have permissions to execute procedures and permissions on some tables.

If the owners are the same then they can execute the procedures regardless of whether they have access to the tables.

They are in the same schema so I can't use schema owners.

I'm thinking that changing the owner for the tables during the restore process is a more robust and practical option than manging permissions on individual procedures.

It's not an ideal situation but one I have to work within at least for the time being.

Jeff Moden SSC Guru Points: 1004645 More actions · Answer 3

Just to be sure...

Anyone with "db_owner" privs on a database can see, use, and manipulate any and all objects in the database, regardless of who owns the individual objects because the fixed "db_owner" role has CONTROL DATABASE privs, which has ALL permissions in the database... regardless of who the owner of a given object may be.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

as_1234 SSCrazy Points: 2881 More actions · Answer 4

Thanks for your advice.

The users concerned aren't in the db_owner group and they are not sysadmins so I don't think they should pick up any owner rights. Before posting the topic I ran the following test:

With the current settings asked a user in the group to a) select from a table they don't have permission on b) Execute a stored procedure which selects from the table. a) Gave permission denied b) Worked

Created a database user with no password.

Move the object of the table to the new user.

Retried the test. a) and b) both gave permission denied.

I think that shows they aren't picking up any ownership and they are only getting access to the table in the current setting due to ownership chaining?

Phil Parkin SSC Guru Points: 247180 More actions · Answer 5

I still don't really understand what you are trying to achieve.

You have a database on server A. Users can access some tables and execute some procs.

You restore the database to server B. You want to have different levels of access to tables and procs on this version of the database.

Is that correct?

How are you controlling access? Are you using object -level GRANTs and DENYs? Are the users the same in both database versions? Are they SQL users, or Windows users?

as_1234 SSCrazy Points: 2881 More actions · Answer 6

Hello,

Apologies for my slow reply.

The accounts are all Windows AD accounts.

On server A they can access some tables and can't execute any stored procedures.

On server B I need them to be able to execute stored procedures.

The procedures change frequently so managing permissions at indiviudal procedure level is not ideal.

I think my testing described in my earlier post shows that changing the owner works (happy to be corrected though) but I am just not 100% sure if using a sql account without login is a good choice of owner as I don't have much experience with them.

Thanks