February 6, 2007 at 6:36 am
Hi,
I have a user that has db_owner database role. When the user creates a table if the 'dbo' is left out of the create table ie CREATE TABLE SALES the owner of the table defaults to the user name (eg if user is PRG then the table owner becomes PRG).
Is there any way of forcing ownership to be dbo? I thought giving the db_owner role would do this.
Any help appreciated.
Many thanks
Pete
Peter Gadsby
Business Intelligence Consultant
www.hgconsult.co.uk
February 6, 2007 at 7:34 am
If the user specifies the owner during creation, it will be so. For instance: CREATE TABLE dbo.Sales. The only other way to do this is to create an alias (like in the 6.5 days), but that method is deprecated.
K. Brian Kelley
@kbriankelley
February 6, 2007 at 7:53 am
beat your users hard around the head until they learn to always qualify an object, it's best practice anyway, so teach them < grin > If only I could remotely wire a user's chair to give a shock for such events!!!
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
February 6, 2007 at 7:55 am
Hi,
Thanks for the replies.... But when you use All tasks - Generate scripts for stored procedures, the dbo does not get added to the CREATE PROCEDURE part of the script (although it does for the delete) this means the user gets set as the owner instead of dbo....
This is very annoying...
Pete
Peter Gadsby
Business Intelligence Consultant
www.hgconsult.co.uk
February 6, 2007 at 8:11 am
as I said you must always qualify objects ..
when you create a proc you should define as:-
create proc dbo.myproc
as
then when you script you get ownership - as I said before, best practice.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
February 6, 2007 at 8:16 am
This was from scripting directly from enteprise manager, not creating a script manually... Surely Enteprise Manager should also qualify the name when creating the stored procedure!
Peter Gadsby
Business Intelligence Consultant
www.hgconsult.co.uk
February 6, 2007 at 8:28 am
Your best bet here is Query Analyzer. It has an option to Qualify object name by its owner under the Table Scripting Options.
K. Brian Kelley
@kbriankelley
February 7, 2007 at 12:32 am
To automatically get the dbo prefix when creating a table, the login must not be a user of the database. You must also use a system administrator login and run sp_changedbowner LOGINNAME in Query Analyzer. Only one login can be dbo (in that sense) of a database.
February 7, 2007 at 12:33 am
- Actualy the only way of creating dbo objects by default is making that user databaseowner !! sp_changedbowner @loginame = 'PRG' ,@map = 'true'
groupmembership is not enough ! (you'll have to remove the user from the db before you can make that user databaseowner)
- Downside of this is that you can only have one databaseowner !
- as already mentioned in the other replies : learn to qualify objects ! so always provide a schema (owner) to the object you want to use.
Offcourse this contradics to the pittfall-mechanism of dbo (searchlist for the object being first the user-id, then dbo). but most dba have been confronted with the maze of having non-dbo-objects and dbo-objects with the same name and the struggle to detect why a certain procedure works with one user and not with the other.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
February 7, 2007 at 1:36 am
in general terms the only thing I can see user owned objects are good for is setting complex permission questions in the mcdba tests!! Ownership becomes more important in sql 2005 where schemas are properly defined, I'm still not convinced having lots of schema owners in a database is a good idea, but no doubt time will tell.
I echo the QA or VS for creating objects - don't use EM to create new objects.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
February 7, 2007 at 3:18 am
Hi,
Just to be clear, I don't use EM to create new objects, I create scripts for all of the databases using EM and store the scripts in Source Safe.
I want to 'train' the developers to use this method of keeping upto date object scripts in Source Safe for change control.
I don't really want to give the developers a powerful user like sa so I am now a little stuck!
Pete
Peter Gadsby
Business Intelligence Consultant
www.hgconsult.co.uk
February 7, 2007 at 7:29 am
Don't use EM. Use QA. You have more scripting options. And you can still get them in SourceSafe. Also, there are 3rd party tools like DBGhost you may want to take a look at.
K. Brian Kelley
@kbriankelley
February 7, 2007 at 7:32 am
If you're using the Principle of Least Privilege, they actually make a lot of sense. Unless object ownership is qualified in every reference, by default SQL Server will attempt to look for an object belonging to said user. Unless said user is dbo, you guessed it, it's going to miss. Only then is it going to look for an object owned by dbo.
Typically we wouldn't want our application coming in as dbo. There are a lot of obvious security risks to this approach, especially if servicing web servers which face the Internet. Therefore, the only way to resolve this practically is to use objects which belong to the user. A better solution is to have a development standard that requires everyone to specify the owner when they are referring to an object, but in large applications this is almost unenforceable.
K. Brian Kelley
@kbriankelley
February 7, 2007 at 7:41 am
It only looks for user objects if you don't qualify the sql, which is best practice, therefore user fred executing select * from mytable will, as you say, look for table owned by fred before dbo.
However best practice says all calls should be qualified so, user fred executing select * from dbo.mytable looks for table owned by dbo not fred. This is well documented by microsoft somewhere or other. I find most clients I visit fully qualify their sql these days, and it's one of the points I raise if they don't. Agreed you don't want to access a database as dbo, although many third party apps do this as default ( another subject matter ) but I disagree you need to be dbo to call dbo owned objects.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
February 7, 2007 at 7:51 am
I'm not saying you need to be dbo to call dbo owned objects. My point is that in large apps a reference may get missed here or there... If everything is being developed against a user other than dbo as an owner (the same user the app is using), then this isn't an issue. I also agree that best practices say to qualify the owner, but I've seen quite a few examples over several companies where ownership isn't being specified, so I know that problem still exists.
K. Brian Kelley
@kbriankelley
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply