Re: SQL Server 2005 Stored Procedure security annoyances

Are there no way to set per database the rights to execute non-system
stored proecedure on a database wide level?

In SQL 2005, you can grant permissions at higher levels, like schema or
database. For example:

GRANT EXECUTE ON SCHEMA::MySchema TO MyRole

I mean it is unrealistic to have to manually set for each SP the rights
because
I am sure I will forget some SP's and probably forget to set the rights
when
I add new SP's in the future. Sure , I can run some wierd query to set
the
rights to all SP's, but that's a pain to lookup as well.

I mean it must be possible to set the SP security settings with minimal
time/effort,
otherwise I can just as well continiue using a account with to broad
rights. db_owner?


Personally, I would still create appropriate roles and grant individual proc
permissions because not all stored procedures are equal. Granting
permissions to all procs may be acceptable when all procedures are read-only
and you have no sensitive data or when granting to an admin role. However,
these are the exceptional cases.

Consider keeping stored procedure scripts files with permissions, preferably
in source control. I think you will soon find that scripts look naked
without GRANTs and you won't forget to assign permissions. Perhaps it's
time to graduate to the next 'maturity level'.

--
Hope this helps.

Dan Guzman
SQL Server MVP

<sune42@xxxxxxxxxxx> wrote in message
news:1158486740.616143.183250@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
hi

I have came to the maturity level to stop using the sa account when I
run
my web-server.

So, I created a new account and added it to the DB_datareader/writer
role.
but I still can't execute my own stored procedures.

Are there no way to set per database the rights to execute non-system
stored proecedure on a database wide level?

I mean it is unrealistic to have to manually set for each SP the rights
because
I am sure I will forget some SP's and probably forget to set the rights
when
I add new SP's in the future. Sure , I can run some wierd query to set
the
rights to all SP's, but that's a pain to lookup as well.

I mean it must be possible to set the SP security settings with minimal
time/effort,
otherwise I can just as well continiue using a account with to broad
rights. db_owner?

We need a default DB_ExecuteSP role that each user can be associated
with!

any ideas?

/andy



.

Relevant Pages

  • Re: db_denydatawriter
    ... perhaps this also gives read write access on the database to this user? ... Resrictive permissions overrides in its own level. ... However, if she has sysadmin right, then she'll be able to modify that data. ... Is it possible she has some admin rights which override DenyWriter (though ...
    (microsoft.public.sqlserver.security)
  • RE: user permissions in a database as a whole or tables
    ... user permissions in a database as a whole or tables ... The way to give permissions to a user is a simple GRANT. ...
    (comp.databases.informix)
  • Re: user permissions in a database as a whole or tables
    ... like PDF better but that was not helping this time either. ... user permissions in a database as a whole or tables ... The way to give permissions to a user is a simple GRANT. ...
    (comp.databases.informix)
  • Re: Issue: LDB Not Being Created
    ... I would totally agree that it is a rights issue (and was convinced it ... I open the database and the LDB gets created. ... file create permissions, then all other users of the network would be able ... However if one user opens up the file does not have correct permissions to ...
    (comp.databases.ms-access)
  • Re: Complete Neophyte Question(s)
    ... No you cannot remove a login from the 'public' role. ... For rights to _use_ objects then the appropriate rights need to be granted ... GRANT SELECT ON dbo.Orders TO OrderViewers ... for a database that is supposed to be secured it is a bad idea ...
    (microsoft.public.sqlserver.security)