Re: Logging in irrespective of the database access settings

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

From: Aaron [SQL Server MVP] (ten.xoc_at_dnartreb.noraa)
Date: 07/16/04 

Date: Fri, 16 Jul 2004 08:58:37 -0400

> As our client-side app controls the permissions, we need each user to have
> grant all access to all tables and sprocs and ticking these manually is a
> no-no.

Why do you have to do this manually for each object? Here is a quick way to
generate GRANT scripts for a specific user to all tables/procs, then you
take the results and execute them.

SELECT 'GRANT EXEC ON '+SPECIFIC_SCHEMA+'.'+ROUTINE_NAME+' TO [someuser]'
    FROM INFORMATION_SCHEMA.ROUTINES

SELECT 'GRANT ALL ON '+TABLE_SCHEMA+'.'+TABLE_NAME+' TO [someuser]'
    FROM INFORMATION_SCHEMA.TABLES

You can also do this in a cursor and execute the GRANT statement using
dynamic SQL, if you can't do the above manually.

> What's the point in having the Database Access section if the System Admin
> under Server Roles allows you to get into anything?.

The same reason you can lock your front door, but leave the key hanging in
the doorknob. I don't understand why users of an app would ever have to be
sysadmin. 

-- 
http://www.aspfaq.com/
(Reverse address to reply.)

Relevant Pages

  • Re: Logging in irrespective of the database access settings
    ... > As our client-side app controls the permissions, we need each user to have ... > grant all access to all tables and sprocs and ticking these manually is a ... generate GRANT scripts for a specific user to all tables/procs, ... You can also do this in a cursor and execute the GRANT statement using ...
    (microsoft.public.sqlserver.setup)
  • Re: [Info-ingres] Re: Remote Command Does not Start
    ... grant select,insert,update,delete on $ingres.remotecmdinview to "alessandro.azzo ... which are required in order to execute this statement. ... raise on dbevent $ingres.rmcmdcmdend to " alessandro.azzone"\p ...
    (comp.databases.ingres)
  • Re: [Info-ingres] Re: Remote Command Does not Start
    ... grant select,insert,update,delete on $ingres.remotecmdinview to "alessandro.azzo ... which are required in order to execute this statement. ... raise on dbevent $ingres.rmcmdcmdend to " alessandro.azzone"\p ...
    (comp.databases.ingres)
  • Re: are the ALL_* views usually viewable by all users?
    ... Even a freshly created user with only CREATE SESSION privilege can ... execute DBMS_UTILITY.NAME_RESOLVE so no special privileges need be ... SQL> grant create session to blerp; ... Grant succeeded. ...
    (comp.databases.oracle.misc)
  • Re: sys.DBMS_DDL.CREATE_WRAPPED what other priviledges are needed?
    ... Is there some other package that I should grant execute permissions to? ... Not as user sys, nor as user with granted execute on SYS.DBMS_DDL. ...
    (comp.databases.oracle.misc)