owner advice

I'm not an expert so please be gentle if I say anyting incorrect in here.
Just looking for advice. Not any specific problem right now. I'm working on
a db that will be run independently in several offices. The owner of every
object is currently dbo. I move it (by doing backup and restore) between me
and another developer. We're both logging in as sa and don't have any
problems. We also don't use dbo in front of any objects when we reference
them in procedures, views and such (except for when referencing UDF's which
seem to require it). Are there any issues we need to be aware of with
respect to this? Are we ok with everythign being dbo and not qualifying
objects with it? We plan on creating another user (not sa) that the front
end app will use to get to the db. That sql user name and pwd will be coded
into the front end before it's compiled. Our tests seem to inidcate that
this works fine but we just want to be sure.
One reason this came to my attention is that I ran into an issue with
another db that I didnt' work on that got moved. I guess the object (an SP I
think) had been created by some other owner and when I went in to modify it
I couldnt' save it without putting dbo in front of every table in the SP. So
I did that but never really looked into what was going on.
Like I said, just looking for advice on this area. I know some of you out
there can help.
Thanks,
Keith"Keith G Hicks" <krh@.comcast.net> wrote in message
news:%23Fs5ugYpFHA.1412@.TK2MSFTNGP09.phx.gbl...
> I'm not an expert so please be gentle if I say anyting incorrect in here.
> Just looking for advice. Not any specific problem right now. I'm working
> on
> a db that will be run independently in several offices. The owner of every
> object is currently dbo. I move it (by doing backup and restore) between
> me
> and another developer. We're both logging in as sa and don't have any
> problems. We also don't use dbo in front of any objects when we reference
> them in procedures, views and such (except for when referencing UDF's
> which
> seem to require it). Are there any issues we need to be aware of with
> respect to this? Are we ok with everythign being dbo and not qualifying
> objects with it? We plan on creating another user (not sa) that the front
> end app will use to get to the db. That sql user name and pwd will be
> coded
> into the front end before it's compiled. Our tests seem to inidcate that
> this works fine but we just want to be sure.
> One reason this came to my attention is that I ran into an issue with
> another db that I didnt' work on that got moved. I guess the object (an SP
> I
> think) had been created by some other owner and when I went in to modify
> it
> I couldnt' save it without putting dbo in front of every table in the SP.
> So
> I did that but never really looked into what was going on.
> Like I said, just looking for advice on this area. I know some of you out
> there can help.
> Thanks,
> Keith
>
What you're doing there seems OK to me, basically everything is owned by
dbo, and it's all in the same database - as simple as you can get.
One thing that I would suggest that you look at when hooking in your
front-end is perhaps not creating a specific user, but instead allowing
people to login to the database using windows authentication - don't give
the user any access, but instead setup an application role which your
program uses, this way your maximise the security of your DB and
application.
Regards
Colin Dawson
www.cjdawson.com|||Also, if you are not the owner and you are executing a stored procedure, you
WILL want to qualify it with dbo. There is a performance hit when you are
not the owner of a stored procedure and you execute it without qualifying
the owner.
See http://support.microsoft.com/defaul...kb;en-us;243586 (look for
"Best Practice")
See also http://support.microsoft.com/defaul...kb;en-us;263889
(look for "More Information")
Mike
"Colin Dawson" <newsgroups@.cjdawson.com> wrote in message
news:u4GNe.92710$G8.85208@.text.news.blueyonder.co.uk...
> "Keith G Hicks" <krh@.comcast.net> wrote in message
> news:%23Fs5ugYpFHA.1412@.TK2MSFTNGP09.phx.gbl...
>
> What you're doing there seems OK to me, basically everything is owned by
> dbo, and it's all in the same database - as simple as you can get.
> One thing that I would suggest that you look at when hooking in your
> front-end is perhaps not creating a specific user, but instead allowing
> people to login to the database using windows authentication - don't give
> the user any access, but instead setup an application role which your
> program uses, this way your maximise the security of your DB and
> application.
> Regards
> Colin Dawson
> www.cjdawson.com
>|||Hi
If you move a database and the SIDs don't match the original logins, then
you will get orphaned users as described in
121120120" target="_blank">http://support.microsoft.com/defaul...r />
121120120
John
"Keith G Hicks" <krh@.comcast.net> wrote in message
news:%23Fs5ugYpFHA.1412@.TK2MSFTNGP09.phx.gbl...
> I'm not an expert so please be gentle if I say anyting incorrect in here.
> Just looking for advice. Not any specific problem right now. I'm working
> on
> a db that will be run independently in several offices. The owner of every
> object is currently dbo. I move it (by doing backup and restore) between
> me
> and another developer. We're both logging in as sa and don't have any
> problems. We also don't use dbo in front of any objects when we reference
> them in procedures, views and such (except for when referencing UDF's
> which
> seem to require it). Are there any issues we need to be aware of with
> respect to this? Are we ok with everythign being dbo and not qualifying
> objects with it? We plan on creating another user (not sa) that the front
> end app will use to get to the db. That sql user name and pwd will be
> coded
> into the front end before it's compiled. Our tests seem to inidcate that
> this works fine but we just want to be sure.
> One reason this came to my attention is that I ran into an issue with
> another db that I didnt' work on that got moved. I guess the object (an SP
> I
> think) had been created by some other owner and when I went in to modify
> it
> I couldnt' save it without putting dbo in front of every table in the SP.
> So
> I did that but never really looked into what was going on.
> Like I said, just looking for advice on this area. I know some of you out
> there can help.
> Thanks,
> Keith
>