Re: Sql permissions headache

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: Sql permissions headache
    ... at the bottom of all of the scripts I create for database object ... apply permissions to that object on creation. ... Read and write permissons are easy to address but stored procs are ...
    (microsoft.public.sqlserver.security)
  • Re: SQL 2000 Windows Authentication - Same User Multiple Groups
    ... App1 contains CRUD functionality using stored procs (EXEC perms on ... UpdateResearch stored procs. ... execute UpdateResearch. ... view-level permissions such that we can permit/deny a database action ...
    (microsoft.public.sqlserver.security)
  • Re: SQL 2000 Windows Authentication - Same User Multiple Groups
    ... App1 contains CRUD functionality using stored procs (EXEC perms on ... Resarch group (of which he is also a member), ... execute UpdateResearch. ... view-level permissions such that we can permit/deny a database ...
    (microsoft.public.sqlserver.security)
  • Re: Permissions
    ... It turns out that some scripts required higher ... permissions that others, but I can't figure out what the difference is. ... PHP exec or from shell commands need execute privilege. ... Apache with suexec: your CGIs run as the same user as the ...
    (comp.lang.php)
  • Re: Need some Cocktail advice
    ... Dudley Henriques wrote: ... I think I understand correctly from researching the help section that the scripts are cumulative; in other words, the weekly does the daily, and the monthly does the weekly as well. ... Secondly, I'm puzzled about when to run permissions and whether running the daily script is even necessary since it seems once a week, then once a month on the scripts seems an adequate maintenance program for the system. ... Leopard still has some bugs in it that will corrupt disks and there's no GUI for the warnings. ...
    (comp.sys.mac.apps)