October 7, 2005 at 12:18 pm
I have some users that create objects, but the owner shows up as "Domain\User" instead of "dbo", even though they have dbo access to the database. It seems that they need to be "system administrator" to have their objects be dbo. I want users to have access to just a few databases, but for those objects to be dbo when they create them. I know that dbo can be specified in a SP for example, but I also have a 3rd party application that creates objects, and I have no control over the code.
Can this be done ?
October 7, 2005 at 12:34 pm
your users need to know to create tables using the proper naming conventions no exceptions
If the table is intended to be shared to all db users. then they should create the table as
create table dbo.Mytable (...)
not
Create table mytable
otherwise
you;ll have to continually follow behind them using
sp_changeobjectowner to change owner to dbo.
October 7, 2005 at 12:36 pm
... and then update the script of the procs, views, functions to include the dbo. Because if you script then out and reapply them you're gonna have to do it all over again.
October 7, 2005 at 1:23 pm
In the days of SQL 6.5 there was only ever one dbo so in order to grant dbo privileges there was an sp_addalias stored procedure to allow many logins to be the dbo.
This stored procedure still exists in SQL 2000.
October 7, 2005 at 1:24 pm
Thanks ... I understand about manually creating tables & SPs. I was hoping there was some sort of administrative way to make objects automatically be "dbo" for them for when objects are created by other means. Recently a developer wanted to use the "copy objects" option of the export data wizard in EM. That's usefule because you can copy a whole bunch of items, including extended properties etc .... I don't see anywhere to specify the owner. And if our 3rd party application creates SPs, I can't put "dbo" into their code.
October 7, 2005 at 1:46 pm
Let us suppose you have 3 logins for your app
Domain\Tom
Domain\Dick
Domain\Harriet
Within your database you would run
exec sp_addalias 'Domain\Tom','dbo'
exec sp_addalias 'Domain\Dick','dbo'
exec sp_addalias 'Domain\Harriet','dbo'
Even though your 3 users log on with their own logins as far as the database is concerned they are all a user called dbo, therefore when they create objects those objects will be created as dbo.object
October 7, 2005 at 1:57 pm
But, in this case: exec sp_addalias 'Domain\Tom','dbo' doesn't the second parm have to be an actual login ? so 'dbo' is a sql login with dbo rights in the database ?
October 7, 2005 at 3:26 pm
No the 2nd parameter is the user within the database.
Normally, within SQL 7 and 2000 a single login can be a single user in any one database.
What we are saying is that multiple logins can be a single user, in this case THE dbo rather than a user with dbo privileges.
Try it and see.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply