filtering with set: reading

I just finished reading several articles on the SQL Server Security Checklis
t
or Best Practices - and I could use some advise. Our ITS group is too small
to have a full time [application] DBA for SQL Servers so only Networking has
SA rights on the servers which in the past was not a problem. But now that
we're starting to develop/implement new Coldfusion and SQL Server Database
applications as we head toward switching from static HTML to a dynamic
database driven multiple server production environment that will use both a
staging and a development server. Since these are new technologies to this
environment and the production servers that only contain public information
(i.e. do not contain high risk data), Development has requested read-only
access to the production and staging servers to look at the data integrity,
check the rev. number on the code, and look at IIS, CF, and SQL logs and
configuration files, as needed. Development has also requested SA rights fo
r
their Sr. Developers only on the development server (i.e not the product
servers) to be used for testing replication, running diagnostics, reviewing
logs, create test applications users/roles to test permissions, test
configuration settings and permission issues (we are also implementing activ
e
directory) but access was denied to both requests. With one month left
before going live with the new production servers on IIS/CF/SQL, development
is concerned with the time delays with testing or replicating problems on th
e
development server and the inability to diagnose production problems with
outdated logs (vs real time monitors of IIS/CF).
The networking staff are competent at network issue but are not DBAs and doe
not have knowledge of how setting and permissions in SQL Server (not to
mention IIS or CF) will affect applications. How should SQL Server
roles/permission be split between Networking and Development on the
Development server in our small environment (note: our Sr Developers have 5
-
25 years of development experience including commercial DB engineering)?hi
there are no hard and fast rules to set a role to the users of the database.
it depends on the policy that u draft.
U need not have a full time DBA, eventhough its advised to have. Senior
developers with good admin skills can take up the role.
the person should have knowledge on taking backups and crash recovery
hope this answers the question
best Regards,
Chandra
http://chanduas.blogspot.com/
http://groups.msn.com/SQLResource/
---
"JA" wrote:

> I just finished reading several articles on the SQL Server Security Checkl
ist
> or Best Practices - and I could use some advise. Our ITS group is too sma
ll
> to have a full time [application] DBA for SQL Servers so only Networking has
> SA rights on the servers which in the past was not a problem. But now tha
t
> we're starting to develop/implement new Coldfusion and SQL Server Database
> applications as we head toward switching from static HTML to a dynamic
> database driven multiple server production environment that will use both
a
> staging and a development server. Since these are new technologies to th
is
> environment and the production servers that only contain public informatio
n
> (i.e. do not contain high risk data), Development has requested read-only
> access to the production and staging servers to look at the data integrity
,
> check the rev. number on the code, and look at IIS, CF, and SQL logs and
> configuration files, as needed. Development has also requested SA rights
for
> their Sr. Developers only on the development server (i.e not the product
> servers) to be used for testing replication, running diagnostics, reviewin
g
> logs, create test applications users/roles to test permissions, test
> configuration settings and permission issues (we are also implementing act
ive
> directory) but access was denied to both requests. With one month left
> before going live with the new production servers on IIS/CF/SQL, developme
nt
> is concerned with the time delays with testing or replicating problems on
the
> development server and the inability to diagnose production problems with
> outdated logs (vs real time monitors of IIS/CF).
> The networking staff are competent at network issue but are not DBAs and d
oe
> not have knowledge of how setting and permissions in SQL Server (not to
> mention IIS or CF) will affect applications. How should SQL Server
> roles/permission be split between Networking and Development on the
> Development server in our small environment (note: our Sr Developers have
5 -
> 25 years of development experience including commercial DB engineering)?|||It sounds like you're between a political rock and a hard place. The
important thing is to save in your CYA file a copy of the memo informing
management that what's going live cannot be adequately tested without
access, and since access has been denied, that they'll have to live with the
possibility of a meltdown. Once a meltdown occurs, you can be absolutely
sure that you will be given all the access you need (and probably more) in
order to fix the problem. (You might want to plan on an all-night debugging
fest, so be sure to stock up on Mountain Dew.)
Another solution would be to request equipment identical to the production
environment that is dedicated and accessible to the ITS group just for
testing. Of course someone will have to pay for the new equipment, and
you'll probably have to delay going live until the new equipment is
configured and testing is completed.
"JA" <JA@.discussions.microsoft.com> wrote in message
news:9E08BED2-6BEA-46B7-966E-64A6ADE1AD19@.microsoft.com...
> I just finished reading several articles on the SQL Server Security
Checklist
> or Best Practices - and I could use some advise. Our ITS group is too
small
> to have a full time [application] DBA for SQL Servers so only Networking
has
> SA rights on the servers which in the past was not a problem. But now
that
> we're starting to develop/implement new Coldfusion and SQL Server Database
> applications as we head toward switching from static HTML to a dynamic
> database driven multiple server production environment that will use both
a
> staging and a development server. Since these are new technologies to
this
> environment and the production servers that only contain public
information
> (i.e. do not contain high risk data), Development has requested read-only
> access to the production and staging servers to look at the data
integrity,
> check the rev. number on the code, and look at IIS, CF, and SQL logs and
> configuration files, as needed. Development has also requested SA rights
for
> their Sr. Developers only on the development server (i.e not the product
> servers) to be used for testing replication, running diagnostics,
reviewing
> logs, create test applications users/roles to test permissions, test
> configuration settings and permission issues (we are also implementing
active
> directory) but access was denied to both requests. With one month left
> before going live with the new production servers on IIS/CF/SQL,
development
> is concerned with the time delays with testing or replicating problems on
the
> development server and the inability to diagnose production problems with
> outdated logs (vs real time monitors of IIS/CF).
> The networking staff are competent at network issue but are not DBAs and
doe
> not have knowledge of how setting and permissions in SQL Server (not to
> mention IIS or CF) will affect applications. How should SQL Server
> roles/permission be split between Networking and Development on the
> Development server in our small environment (note: our Sr Developers have
5 -
> 25 years of development experience including commercial DB engineering)?|||SA rights on a development box does not sound unreasonable; in fact, I
recommend it, for many of the reasons you list above. Developers need
their own space to break stuff, so that they understand how NOT to
break it in production.
I work in a small shop as well, and currently I serve as both
production and development DBA; we're hiring a production DBA, but
until then I have to document every little thing I do to make sure that
there is a record for accountability depending on the hat I'm wearing.
I think it's OK for developers to have read-only access to
llimited-risk data in production, and they should have full control
over their development environment. And you can tell your bosses I
said so :)
Someone on the Interwebs believes in your cause.
Stu|||On Fri, 29 Jul 2005 22:56:02 -0700, JA wrote:
(snip)
>The networking staff are competent at network issue but are not DBAs and do
e
>not have knowledge of how setting and permissions in SQL Server (not to
>mention IIS or CF) will affect applications. How should SQL Server
>roles/permission be split between Networking and Development on the
>Development server in our small environment (note: our Sr Developers have 5
-
>25 years of development experience including commercial DB engineering)?
Hi JA,
I tend to agree with Stu: SA rights on the dev. server for experienced
DB developers and read rights to non-sensitive stuff on the prod server
sounds reasonable.
On the other hand, the networking staff has been assigned the
responsibility to keep the databases up and running and to protect data
from from unauthorized spreading (I'm sure that this is a terrible
sentence, but I don't know how to put it in correct English - I hope you
do understand what I'm trying to say).
And since it's THEIR responsibility, they are free to take whatever
measures THEY think are needed to accomplish that goals.
Many years ago, I had to do some major reshuffling on a SQL Server
implementation. The sa access I had was enough for about half the things
I needed to do (like shutting down the service, or creating a new file),
but many other tasks (like restarting the service, and deleting files no
longer needed) were only available for the network admins. Of course, I
requested access as network admin, to streamline the process. And of
course, I didn't get it. So I phoned the network admins each time I
needed them to do something:
"Hi, Hugo here - could you restart the SQL Server service please?"
"Hi, Hugo here - could you restart the SQL Server service please?"
"Hi, Hugo here - could you delete file XXX on server YYY please?"
"Hi, Hugo here - could you restart the SQL Server service please?"
"Hi, Hugo here - file AAA has to be moved from drive BBB to CCC."
"Hi, Hugo here - could you restart the SQL Server service please?"
"Hi, Hugo here - could you restart the SQL Server service please?"
"Hi, Hugo here - I requested a restart of the SQL Server service some 5
minutes ago, but doesn't seem to be running"
And so on.
Guess how long it took before I DID get some extra rights?
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||When I worked help desk, we used to hate guys like you :)
Stu|||On 30 Jul 2005 16:38:27 -0700, Stu wrote:

>When I worked help desk, we used to hate guys like you :)
Hey! I was only trying to get my job done, you know!
(mumble, whine, mumble)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

When I tried to grant a Select permission on a table to a
user, it was blocked by any users who were currently
reading that table.
The grant command in QA issued lock Sch-M (Schema
modification) lock. Why is it issued Sch-M lock ?
Is it mean I cannot grant permission only any table while
users access it ?Hi Johnny,
Changes to schema information can not occur while the object is in use. I
suggest you continue to try of schedule it over night.
As an alternative to using SQL users ID you could use Windows groups to
secure your tables or use Database roles and add your users to the role
I hope this helps
regards
Greg O MCSD
http://www.ag-software.com/ags_scribe_index.asp. SQL Scribe Documentation
Builder, the quickest way to document your database
http://www.ag-software.com/ags_SSEPE_index.asp. AGS SQL Server Extended
Property Extended properties manager for SQL 2000
http://www.ag-software.com/IconExtractionProgram.asp. Free icon extraction
program
http://www.ag-software.com. Free programming tools
"Johnny" <jtao@.ssc.nsw.gov.au> wrote in message
news:058701c3b569$b81a9810$a301280a@.phx.gbl...
> When I tried to grant a Select permission on a table to a
> user, it was blocked by any users who were currently
> reading that table.
> The grant command in QA issued lock Sch-M (Schema
> modification) lock. Why is it issued Sch-M lock ?
> Is it mean I cannot grant permission only any table while
> users access it ?
>|||1) Why does grant permission on table require Sch-M lock ?
I am sure it is regular task to grant table permission to
users. I can't just wait for scheduling overnight.
2) I also tried to create a database role. But when I
grant Select permission to that role, it was blocked.
Running SQL 7 & SP3 on Windows 2000.|||Johnny,
1) You have a good point but the fact remain it does and your can do it
until you can lock the table. Think of it this way, if you were changing
the permissions to remove users (which may be accessing the Table) with
permissions. Then you can understand you would need to lock the schema.
The process doesn't know you are adding until after.
2) Yes the time you grant permissions to the role again you need a lock but
after what you do is add users to roles this doesn't require a lock at all.
So you can do this while the table is in use.
I hope this helps
regards
Greg O MCSD
http://www.ag-software.com/ags_scribe_index.asp. SQL Scribe Documentation
Builder, the quickest way to document your database
http://www.ag-software.com/ags_SSEPE_index.asp. AGS SQL Server Extended
Property Extended properties manager for SQL 2000
http://www.ag-software.com/IconExtractionProgram.asp. Free icon extraction
program
http://www.ag-software.com. Free programming tools
"Johnny" <jtao@.ssc.nsw.gov.au> wrote in message
news:049101c3b56e$5e304e80$a401280a@.phx.gbl...
> 1) Why does grant permission on table require Sch-M lock ?
> I am sure it is regular task to grant table permission to
> users. I can't just wait for scheduling overnight.
> 2) I also tried to create a database role. But when I
> grant Select permission to that role, it was blocked.
> Running SQL 7 & SP3 on Windows 2000.

Thursday, March 29, 2012

Granting SA rights to Sr. Developers in Small ITS Groups when ther

Monday, March 26, 2012

Grant permission (Sch-M lock)

Monday, March 12, 2012

Good reading

filtering with set

Blog Archive

About Me