Multiple Database Security - How to handle

From: Eric Brasher (longday24_at_hotmail.com)
Date: 09/17/03 

Date: Tue, 16 Sep 2003 21:00:39 -0700

I would like to get ideas on how dba's are controlling
security for multiple databases.

We have a datamart (DM) containing all of the Human
Resources information from pay information to sick leave.

We have databases on the same server that house
processing data for applications. Like a Manager system
(MIP) that allows managers to control an incentive pay
program. Another is a United Way contributions
application (UWAY)

The problems are security maintenance. I will have 5
users using MIP that will need access to a stored
procedure that accesses DM to get employee info. To get
the security setup the 5 users need execute permissions
to the store procedure and permissions to the DM for the
tables accessed by the stored procedure.

Now add UWAY. 5 more users using UWAY will need access to
a stored procedure that accesses DM to get employee info.
To get the security setup the 5 users need execute
permissions to the store procedure and permissions to the
DM for the tables accessed by the stored procedure.

The security for the DM is getting crowded. Is there a
method to simplify this? Maybe if the stored procedure
has execute permissions then the procedure is executed as
admin. I want to maintain the most secure method
possible, but I can give

Relevant Pages

  • Re: Is there cascading permission?
    ... Michael Hockstein ... Security -Giving Permissions through Stored Procedures ... if a user has permission to execute a stored procedure which in turn ...
    (microsoft.public.sqlserver.security)
  • Re: Is there cascading permission?
    ... Security -Giving Permissions through Stored Procedures ... if a user has permission to execute a stored procedure which in turn ...
    (microsoft.public.sqlserver.security)
  • Re: Table Type Permissions
    ... Users need EXECUTE permissions in the table type in order to use the type to pass table-valued parameters to a stored procedure. ... REFERENCES permission on the type is needed for a user to create a stored procedure that uses the type as a parameter. ... The SQL 2008 BOL topic on GRANTing permissions to TYPEs is not explicit on what the results of granting specific permisisons are. ...
    (microsoft.public.sqlserver.server)
  • app role has exec permission but still cant execute sp
    ... I've set up an application role in my SQL2K database as ... well as some users with no permissions. ... (including execute permissions) ... when I try to execute a stored procedure through ...
    (microsoft.public.sqlserver.security)
  • Re: Stored Procedure and Table permissions
    ... execute permissions on the stored procedure. ... >given the user EXEC permission for the stored procedure. ...
    (microsoft.public.sqlserver.security)