Developer design Best Practices

I have about twenty three MSSQL servers in use and need to consolidate those
down to as few as possible. What I would like to do is haev two nodes for
production and two nodes for development. The main concern my developers keep
throwing at me is that they need a generic account to use for creating tables
and stored procedures and the like so when they code them for web use they
don't have to worry about who owns the table. In thepast they utilized this
approacha and as soon as i went to tighten down on the logins they started
complaining about needing to have this generic account. Can Someone help me
with a guideline white paper or some resources to identify the best way to
payout both the server end for development and production and then the
security to go along with that design. These servers would only be used in an
intranet environment although the databases themselves may eventually migrate
to a dmz. Thanks in advanceGkurtas wrote:
> I have about twenty three MSSQL servers in use and need to
> consolidate those down to as few as possible. What I would like to do
> is haev two nodes for production and two nodes for development. The
> main concern my developers keep throwing at me is that they need a
> generic account to use for creating tables and stored procedures and
> the like so when they code them for web use they don't have to worry
> about who owns the table. In thepast they utilized this approacha and
> as soon as i went to tighten down on the logins they started
> complaining about needing to have this generic account. Can Someone
> help me with a guideline white paper or some resources to identify
> the best way to payout both the server end for development and
> production and then the security to go along with that design. These
> servers would only be used in an intranet environment although the
> databases themselves may eventually migrate to a dmz. Thanks in
> advance
Are you saying the developers want an admistrator account for the
production servers? I've often been on projects where the lead developer
on a project would have an admin account on the development server, but
usually other developers do not. I wouldn't see why more than one or two
project managers or senior developers would need admin rights on the dev
server. On a production server, no way.
--
David G.|||In addition to David's post which I couldn't agree more.
My understanding is that you are talking about object owner, i.e. special
database user. It is a good practice if you have only one owner for all
objects. This way you don't have a broken ownership chain when a sp or a
view use table or other object. This way SQL Server checks permissions on
the topmost object in the chain only. This is not only performance gain;
administration is easier as well, since you have to grant permissions on the
topmost objects only. Additionally you get better security, because you can
have a layer of sp's and views between application/users and tables. So, if
you decide for one owner only, then I suggest you to use dbo, as this user
can't be dropped and leave orphaned objects.
--
Dejan Sarka, SQL Server MVP
Associate Mentor
Solid Quality Learning
More than just Training
www.SolidQualityLearning.com
"Gkurtas" <Gkurtas@.discussions.microsoft.com> wrote in message
news:944EBAAD-B188-4D54-84CF-56AE30610D59@.microsoft.com...
> I have about twenty three MSSQL servers in use and need to consolidate
those
> down to as few as possible. What I would like to do is haev two nodes for
> production and two nodes for development. The main concern my developers
keep
> throwing at me is that they need a generic account to use for creating
tables
> and stored procedures and the like so when they code them for web use they
> don't have to worry about who owns the table. In thepast they utilized
this
> approacha and as soon as i went to tighten down on the logins they started
> complaining about needing to have this generic account. Can Someone help
me
> with a guideline white paper or some resources to identify the best way to
> payout both the server end for development and production and then the
> security to go along with that design. These servers would only be used in
an
> intranet environment although the databases themselves may eventually
migrate
> to a dmz. Thanks in advance|||Hi,
I agree with the two post's :). My Opinion: Dev teams
should not connect to production servers, there must be a
DBA Team that checks and runs all DDL statements on
production environments. This way the DBA team can control
all the database structure and you have a central point to
alter production servers. This could lead to a more
effective checking and control. On the other hand you may
choose to have dbo ownership in all objects since the
admin accounts are all from the DBA team or for instance
you could create an different account just for objects
ownership an either protect the account just for DBA team
use.
Hope this helps :)
Regards
>--Original Message--
>In addition to David's post which I couldn't agree more.
>My understanding is that you are talking about object
owner, i.e. special
>database user. It is a good practice if you have only one
owner for all
>objects. This way you don't have a broken ownership chain
when a sp or a
>view use table or other object. This way SQL Server
checks permissions on
>the topmost object in the chain only. This is not only
performance gain;
>administration is easier as well, since you have to grant
permissions on the
>topmost objects only. Additionally you get better
security, because you can
>have a layer of sp's and views between application/users
and tables. So, if
>you decide for one owner only, then I suggest you to use
dbo, as this user
>can't be dropped and leave orphaned objects.
>--
>Dejan Sarka, SQL Server MVP
>Associate Mentor
>Solid Quality Learning
>More than just Training
>www.SolidQualityLearning.com
>"Gkurtas" <Gkurtas@.discussions.microsoft.com> wrote in
message
>news:944EBAAD-B188-4D54-84CF-56AE30610D59@.microsoft.com...
>> I have about twenty three MSSQL servers in use and need
to consolidate
>those
>> down to as few as possible. What I would like to do is
haev two nodes for
>> production and two nodes for development. The main
concern my developers
>keep
>> throwing at me is that they need a generic account to
use for creating
>tables
>> and stored procedures and the like so when they code
them for web use they
>> don't have to worry about who owns the table. In
thepast they utilized
>this
>> approacha and as soon as i went to tighten down on the
logins they started
>> complaining about needing to have this generic account.
Can Someone help
>me
>> with a guideline white paper or some resources to
identify the best way to
>> payout both the server end for development and
production and then the
>> security to go along with that design. These servers
would only be used in
>an
>> intranet environment although the databases themselves
may eventually
>migrate
>> to a dmz. Thanks in advance
>
>.
>|||I am interested in how you are going to go about consolidating 23 servers
down to 4.
Are you going to use more beefier server/storage to host multiple database
in 1 server or multiple instances of SQL Server, or utilize VMs to run
non-production SQL Server?
"Gkurtas" <Gkurtas@.discussions.microsoft.com> wrote
>I have about twenty three MSSQL servers in use and need to consolidate
>those
> down to as few as possible. What I would like to do is haev two nodes for
> production and two nodes for development.|||I have exactly this issue, as well.
We have a lot of contractors come in and devlop. In the past, they had
their NT Login and were put into the dbo role.
However, this creates a LOT of problems once the contractor leaves. We have
to change the owner of all the objects before we can drop the contractor. My
predecessor simply left the logins active. (!!)
So, what you are saying is you give them the dbo password and let them
connect as that user? How then do you know who did what?
This is only for Dev. Developers are not given access to production.
Limited access to test.|||Amanda wrote:
> Just to clarify - currently all our databases are owned by different
> people, some of them gone, and objects within the database are owned
> by different people, some of them gone. I would like to have one
> owner for all of them to simplify things, and what I have done
> previously is have a user 'dbo' who is assigned to the dbo role. But
> then, I haven't had this many developers accessing things before.
> "Amanda" wrote:
>> I have exactly this issue, as well.
>> We have a lot of contractors come in and devlop. In the past, they
>> had their NT Login and were put into the dbo role.
>> However, this creates a LOT of problems once the contractor leaves.
>> We have to change the owner of all the objects before we can drop
>> the contractor. My predecessor simply left the logins active. (!!)
>> So, what you are saying is you give them the dbo password and let
>> them connect as that user? How then do you know who did what?
>> This is only for Dev. Developers are not given access to production.
>> Limited access to test.
You can let developers create procedures under their own user ids for
testing purposes. When they are ready to have the procedure elevated to
dbo status in the development database (once it's been tested), they
could ask the senior developer or dba assigned to the project. They
should then remove the one they own to avoid execution problems during
testing.
In general, I would say all objects in a database should be dbo owned by
the time you hit production. In fact, it's faster to execute procedures
using the "dbo." prefix rather than leaving it off if you are not the
dbo (as is the case for users in a production environment). This avoids
any potential ownership issues as well.
--
David G.|||Amanda wrote:
> OK - this is REALLY freaky.
> Since there is only me as a dba, I can't go with the option of
> scripting all their objects in dev. We do that for production and
> test - they develop them and then I move them, and the owner is dbo.
> There is NO DBO User in our dev database!!
> It simply doesn't show up in the GUI. Of course it exists in the
> system table.
> I was planning to map a PIMDevDBO login to the "dbo" user. I'm sure
> this can be done via SQL, but it is pretty freaky that dbo isn't
> showing up.
> we have moved this database all over - but I've never seen this happen
> before. When I run sp_change_users_login it doesn't show up in
> report, nor can I fix it.
> Do you guys think I should be concerned'
I adminit it can be a little confusing. You are correct in that "dbo" is
not really a user in the system. When an object is owned by "dbo" it
just means it is owned by the database owner, which is normally a system
administrator.
By logging into a database as "sa" or an administrator account or a
member of the database owner group, you'll end up creating objects as
"dbo" if you do not specify an owner in the create statement. If you
login as a user that can create tables and specify the user name before
the object name, you end up creating a table owned by that user name.
Someone should be monitoring all the database object creation in
development to make sure it is done properly. It's not always an option
to have a busy dba monitoring development servers. In this case, I
suggest you designate a project manager or senior developer to perform
object creation in the development database as "dbo" owned objects.
Developers create procedures using their own IDs and when they are ready
tell someone on the team to compile as dbo. That's one option.
I realize this adds a level of overhead to development for those
developers who do not have proper rights. But with any project there is
an element of overhead designed to prevent accidents like someone
dropping tables or procedures accidentally.
If you want to mitigate this somewhat, you can let all developers create
procedures, but only allow senior developers the right to manage tables
and views.
In any case, this is sometimes a project manager's decision and maybe
you should defer to that person (of course, give him/her your
recommendations).
In any case, no access to production, except possibly the project
manager. Remember that if a developer gets on the production database
and messes something up, you inherit the problem and the responsibility
and all blame... well, need I say more.
--
David G.|||The database owner login is a special case and is not specified like a
regular user. You can execute sp_changedbowner to specify the owner or
correct the mapping following a restore or attach:
USE MyDatabase
EXEC sp_changedbowner 'PIMDevDBO'
GO
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Amanda" <Amanda@.discussions.microsoft.com> wrote in message
news:C9078D21-D0C3-468E-A328-FC0AB622B95F@.microsoft.com...
> OK - this is REALLY freaky.
> Since there is only me as a dba, I can't go with the option of scripting
> all
> their objects in dev. We do that for production and test - they develop
> them
> and then I move them, and the owner is dbo.
> There is NO DBO User in our dev database!!
> It simply doesn't show up in the GUI. Of course it exists in the system
> table.
> I was planning to map a PIMDevDBO login to the "dbo" user. I'm sure this
> can be done via SQL, but it is pretty freaky that dbo isn't showing up.
> we have moved this database all over - but I've never seen this happen
> before. When I run sp_change_users_login it doesn't show up in report,
> nor
> can I fix it.
> Do you guys think I should be concerned'
>