Re: Stored Procedures and Security

From: Scott Meddows (scott_meddows_no_spm_at_tsged-removeme.com)
Date: 07/20/04 

Date: Tue, 20 Jul 2004 09:52:14 -0500

I think that's my problem. We are just starting to really become a med sized IT dept (still relatively new) and different
databases are owned by different people (In fact, database owner isn't even assigned in one of the databases!). Many of the
problems occur when you have to cross over to different catalogs.

Thanks a lot, Wex!! I think you solved my problem.

"Wex" <anonymous@discussions.microsoft.com> wrote in message news:090301c46e67$26b353f0$a601280a@phx.gbl...
> You are mostly correct in your assumption. The one
> exception is what is called "Chain of Ownership." In
> short, one owner cannot grant access to something that is
> owned by another person. If all the objects (views and
> stored procedures in this case) are owned by the same user
> (dbo is suggested) then all you have to do is grant exec
> rights. If different users own the view and procedure,
> you will have to take the additional steps of adding the
> select etc permissions.
> >-----Original Message-----
> >Okay, another big question (This one is probably more
> specific to my SQL Server instance :) ). It was always
> my understanding that
> >SPs are a great way to wrap up SQL commands without
> having to give security everywhere in the DB. The
> problem that I have is that
> >I cannot only give EXEC permissions to a login on my SQL
> box but I have to give select permissions and update
> permissions and all
> >other sorts of permissions to the connected user for all
> the SELECT and UPDATE commands that are executed in the SP!
> >
> >Shouldn't I just have to give EXEC to the SP and be done
> with it?
> >
> >Are there some settings that change this?
> >
> >Thanks
> >Scott
> >
> >
> >.
> >

Relevant Pages

  • Re: one SQL DB getting info from another - user setup?
    ... It appears from your narrative that the Main and DB1 databases ... EXEC sp_helpdb 'Main' ... SQL 2000 SP3 and is turned off by default. ... > In the permissions for Main, USER1 and USER2 are listed, but have NO ...
    (microsoft.public.sqlserver.security)
  • Determining all permissions for an account
    ... How is it possible to view all permissions (insert, update, delete, exec, ... etc.) for all objects in all databases for a specific account? ...
    (microsoft.public.sqlserver.security)
  • Re: Permissions!
    ... be aware that 'dbo' is a special user that has full permissions. ... Database permissions are not checked for the 'dbo' user so it serves no ... You will need individual logins/users so that SQL Server can identify users ... EXEC sp_addrole 'Role1' ...
    (microsoft.public.sqlserver.security)
  • RE: missing system.mdw file
    ... permissions to create the file, ... >The registry key is not missing. ... Running the SCAN program from the Windows ... >>| I cannot create any new databases in Access 2002 on ...
    (microsoft.public.access.setupconfig)
  • Re: Permission in database
    ... You can create database roles and assign object permissions to these ... EXEC sp_addrole 'Administrators' ... GRANT SELECT, INSERT, UPDATE, DELETE ON Sales TO Administrators ...
    (microsoft.public.sqlserver.security)