Re: SQL Server 2005 Database Security

There are several ways you can achieve this in SQL Server 2005:

(1) You can have the procedures signed with certificates that grant them
access to the tables, and then you just need to grant execute permission on
the procedures to the users
(2) You can have the proceduures impersonate a principal that has access to
the tables, and then you just need to grant execute permission on the
procedures to the users
(3) You can use ownership chaining by having the procedures being owned by
the same principal as the tables, and then you just need to grant execute
permission on the procedures to the users

Thanks

--
Laurentiu Cristofor [MSFT]
Software Development Engineer
SQL Server Engine
http://blogs.msdn.com/lcris/

This posting is provided "AS IS" with no warranties, and confers no rights.

"Victor" <Victor@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:BE15EE7D-39C3-4DD7-93D0-C72D7CAD7E04@xxxxxxxxxxxxxxxx
Hello,

I would like to provide a level of security for a database by allowing a
login just to call a set of stored procedures in the database and not be
able
to access the tables directly.

Looks to me like a very basic thing to ask for. However, there doesn't
seem
to be an obvious way to achieve this. If one of the stored procedures
reads/writes from/to a table, I have to grant the appropriate permissions
to
that login and therefore that login can perform any read/write from/to the
table - without being restricted to the way the stored procedure does
these
operations.

Can someone (maybe someone in Microsoft) explain how to achieve what I
want
- or, alternatively state that you just can't do this in SQL Server 2005?

Thanks,

Victor



.

Relevant Pages

  • Re: No db access after publishing web site
    ... GRANT UPDATE TO ... If I detach and attach this database on a different PC (according that PC ... Cannot open database "pago" requested by the login. ... Are you detaching/attaching the SQL Server Express database correctly ...
    (microsoft.public.dotnet.framework.aspnet)
  • Re: Users cannot view stored procedure text
    ... text of the stored procedures, functions, etc. ... You need to grant them VIEW DEFINITION on the procedures. ... Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books ... ...
    (comp.databases.ms-sqlserver)
  • Re: Users cannot view stored procedure text
    ... text of the stored procedures, functions, etc. ... You need to grant them VIEW DEFINITION on the procedures. ... Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx ... Books Online for SQL Server 2005 at ...
    (comp.databases.ms-sqlserver)
  • Re: Stored Procs and db_owner
    ... > I have some users that I need to run stored procedures, ... > How do I give them access to run the stored procs without giving them ... and then grant access to the role instead. ... Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx ...
    (comp.databases.ms-sqlserver)
  • Re: user defined Role - HELP
    ... Grant ALTER TABLE on tblReportNums to 'UM Case Mgmt' ... Grant the role ALTER permission on the table. ... Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx ...
    (microsoft.public.sqlserver.security)