Re: Sql permissions headache

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









.

Relevant Pages

  • Re: how to restrict users to search in their own Organizational Unit
    ... candidate for scripting IF the process makes sense at ... If MOSS Admins need to administer users and/or group membership, just create a mmc console and delegate the proper rights for them. ... Now, because you're dealing with many users, my recommendation is to create THE NECESARY Security Groups in each OU and related them with your MOSS2007 existing security groups, in future when someone creates some user, you just have to add that user to the necessary group and that user will be given the necessary permissions. ...
    (microsoft.public.windows.server.active_directory)
  • Re: Sql permissions headache
    ... sync the databases and stored procs. ... By scripting all of your database objects and the permissions ... Knowing which permissions to assign each group is not going to be easy... ...
    (microsoft.public.sqlserver.security)
  • Re: Creating Multiple FTP Users and Containers (2000 Server + IIS)
    ... > IIS Administrator for an FTP site. ... > complete Windows Scripting novice so I'm hoping that I can get some help ... > What I am even more unsure of is scripting the creation of Virtual Folders ... The permissions will need to be set to ...
    (microsoft.public.windows.server.scripting)
  • Re: is it possible to move table(s) from one db to another?
    ... If you need to move the table, with structure intact (indexes, ... with some cleanup work on setting defaults, permissions, etc. ... >> It seems tin needs to move the table from one database to another ... >> If you need the indexes then you have to create the indexes by scripting ...
    (microsoft.public.sqlserver.server)
  • Re: Creating Multiple FTP Users and Containers (2000 Server + IIS)
    ... >complete Windows Scripting novice so I’m hoping that I can get some help on ... >What I am even more unsure of is scripting the creation of Virtual Folders ... The permissions will need to be set to ... >account will need to be given ‘modify’ permissions (everything except ‘full ...
    (microsoft.public.windows.server.scripting)