Re: Sql permissions headache



Mike,

You wrote....

currently I support about 1,900 of them


!!! Wow! Give youself a pat on the back for surviving that experience.

We typically have Dev, QA, and Prod servers. We use the tools by RedGate to
sync the databases and stored procs. It's amazing to me how everyone has
different approaches to solving the same problems. It looks like the software
world is still closer to the Wild West show than a "blueprint".


Bill







"Mike C#" wrote:

Hi Bill, not sure what your process is, but in the process I generally use
there is great value to scripting everything. Usually the process is to
move database changes from a Development Environment to a QA Environment to
Production. By scripting all of your database objects and the permissions
that go with them you ensure that the database you tested with in
Development is stucturally the same as the one in QA, and the one in QA is
the same as the one in Production. It also makes it easier to go back and
change things on a one-off (bug fixes, minor permission changes, efficiency
tweaks, etc.) if you script your objects. Finally, if you have more than
one server (currently I support about 1,900 of them) it makes it much easier
to roll out changes.

Knowing which permissions to assign each group is not going to be easy...
You'll have to go through and figure that out, but once it's done you can
set up a role for each group of permissions and assign users to those roles.
That keeps you from having to set the permissions on each user individually.
It's going to be some work up front, but will be much easier to maintain
down the road.

"bill" <bill@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:B756AFC8-3590-4201-863A-0780C1B82B42@xxxxxxxxxxxxxxxx
Many of our users, but not all, are in groups (but we are going in that
direction.) Most of the users were created under sql 2000 EM so we have no
scripts as such. Is there significant value to scripting them all? And
even
if we do script them, the big problem is knowing was rights to assign
them.
(I mentioned stored procs as a particularly painful point.)


Bill


"Mike C#" wrote:

Well, at the bottom of all of the scripts I create for database object
creation (tables, views, stored procs, etc.), I put a GRANT statement to
apply permissions to that object on creation. Are your users already
grouped together under roles for each application at least? If so,
GRANTing
object permissions at the end of your object creation scripts should be a
(fairly) trivial matter.

"bill" <bill@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:30625C86-D80C-494D-AB8E-63566A27AD93@xxxxxxxxxxxxxxxx
We are actually using both. We have some older systems that don't
recognize
Windows authentication.

Bill

"Mike C#" wrote:

Are you using Integrated security or SQL Authentication?

"bill" <bill@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:482EB4F8-7055-405A-AD5F-F2B3A6D68AE1@xxxxxxxxxxxxxxxx
In many of our older sql databases I find that the users were all
made
"dbo".
I was told this is because no one really knew the minimum
permissions
needed
by the apps that accessed the database. (I'm afraid that this
practice
may
creep into production databses.)

Read and write permissons are easy to address but stored procs are
harder.
We are always adding new ones to a DB and, if we don't make all
users
dbo,
we
need to explicitly add permissions to each stored proc. At least
that's
what
I think we need to do.

Can you suggest a better approach to determine the minimum
permissions
needed to access a database?

Bill










.