Re: SP_EXECUTESQL Security in 2005

Hi,

Your DBA changed the security rules so that a user can only execute stored
procedures and no longer have rights to the tables and views.
We are also developing our websites and applications with this rule.
So we also bumped into your problem.
We've been helped by the content in the links of Erland Sommarskog (thx).
Basicaly what you have to do is :
- create a certificate in the database
- create a user from that certificate
- give that user the necessary rights on your tables/views that are
necessary to execute your sp_executesql statement
- sign your stored procedure with that certificate ( add signature to ...)


"Erland Sommarskog" wrote:

DigHazuse (DigHazuse@xxxxxxxxxxxxxxxxxxxxxxxxx) writes:
Thank you for the link ... I had read that link before and it was helpful.

However, I believe I already understand why it is failing (my take):
Since the Impersonating User only has access to run a SP, and since
SP_EXECUTESQL is really building/creating and running a separate SP it
does not have access to run it. (The error is receives is that is does
not have access to the Object "Whatever Table"), This makes sense to
me.

My question really is more of a:
Since the DBA only wants to grant access to the Impersonating User to
execute the SP (which is understandable) and since I want to (in certain
occasions) create Dynamic SQL (SP_EXECUTESQL) .... how can we make both
situations work where we're both happy?

First, I don't really know what your impersonating user comes from, but
I suppose that is specific to your setup. Maybe you could give some more
details, in case this could be relevant for the issue?

The solution I recommend in my article is to sign the procedures with a
certificate. Any reason this would not work for you? Of course, you would
still need to convince your DBA that this is a sound way to go.

--
Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

.

Relevant Pages

  • Re: Cross-database execution permissions with certificates and sch
    ... Whatever principal you impersonate with EXECUTE AS must have a security context in both databases but it doesn't need to be dbo. ... CREATE USER dispatcher ... The reason EXECUTE AS OWNER works with dbo as the schema owner is that 1) the certificate is a trusted authenticator because you granted AUTHENTICATE and 2) the impersonated dbo principal exists in dispatchDB by virtue of the fact that both databases are owed by the same server principal. ... You can still use the certificate as the authenticator as long as the impersonated principal has a security context in the referenced database as well. ...
    (microsoft.public.sqlserver.security)
  • Re: Cross-database execution permissions with certificates and sch
    ... activated user cannot access objects in other schemas in this database. ... injection is defended against, however, the threat exists anywhere EXECUTE ... CREATE USER dispatcher ... the certificate is a trusted authenticator because you granted AUTHENTICATE ...
    (microsoft.public.sqlserver.security)
  • Re: Execute Persmission denied on object sp_OACreate
    ... > SQL Server is creating a job behind the scenes. ... > permissions. ... > SA account password and gaining access to the database. ... >>> How can get a user permissions to execute these stored procedures ...
    (microsoft.public.sqlserver.security)
  • Cross-database execution permissions with certificates and schemas
    ... make the dispatcher user the owner (EXEC sp_addrolemember N'db_owner', ... Discard the private key from the certificate, ... make the provider user the owner (EXEC sp_addrolemember N'db_owner', ... execute [dispatch] ...
    (microsoft.public.sqlserver.security)
  • Re: sql transactions per second
    ... good sense of app server and web server hit counts and capacity needs, ... SQL Server to the max, and what their SQL transaction per second count is. ... before all other hardware constrains growth). ... Be prepared to hire a really good DBA who knows how ...
    (microsoft.public.sqlserver.server)