how to grant impersonate dbo permissions?

[unknown]

Developer needs to create stored procedures that include 'with exec as 'dbo'
on sql server 2005

from bol

To specify EXECUTE AS on a login, the caller must have IMPERSONATE
permissions on the specified login name. To specify EXECUTE AS on a database
user, the caller must have IMPERSONATE permissions on the specified user
name.

Thanks

[Erland Sommarskog]

Post by unknown
Developer needs to create stored procedures that include 'with exec as
'dbo' on sql server 2005
from bol
To specify EXECUTE AS on a login, the caller must have IMPERSONATE
permissions on the specified login name. To specify EXECUTE AS on a
database user, the caller must have IMPERSONATE permissions on the
specified user name.

GRANT IMPERSONATE ON USER::dbo TO developer

But I would recommend strongly that you don't do this. This means that
you give the developer the rights to do everything the database owner can
do.

Furthermore, EXECUTE AS 'dbo' is also a very bad idea. Identify exactly
what is going on in that stored procedure and what extra permissions
that are required. Then create a loginless user (CREATE USER frits
WITHOUT LOGIN), and granr this user the reqiured permissions. Then
use that user in the EXECUTE AS clause.

Even better is to sign the procedure with a certificate and create a
user from the certificate that has the required rights.

I have a longer article on my web site that discusses EXECUTE AS and
procedure-signing in detail: http://www.sommarskog.se/grantperm.html.

[unknown]

Wow excellent response! Thanks.

What we have is an internet app that has an sql login i_user that can only
execute stored procedures, no select statements or anything else. But some
stored procedures and triggers use dynamic sql and so I put with the with
execute as 'dbo' statement in those so the applications i_user login can
execute those. I will use your article to redo this. Again, thanks.

Post by Erland Sommarskog

Post by unknown
Developer needs to create stored procedures that include 'with exec as
'dbo' on sql server 2005
from bol
To specify EXECUTE AS on a login, the caller must have IMPERSONATE
permissions on the specified login name. To specify EXECUTE AS on a
database user, the caller must have IMPERSONATE permissions on the
specified user name.

GRANT IMPERSONATE ON USER::dbo TO developer
But I would recommend strongly that you don't do this. This means that
you give the developer the rights to do everything the database owner can
do.
Furthermore, EXECUTE AS 'dbo' is also a very bad idea. Identify exactly
what is going on in that stored procedure and what extra permissions
that are required. Then create a loginless user (CREATE USER frits
WITHOUT LOGIN), and granr this user the reqiured permissions. Then
use that user in the EXECUTE AS clause.
Even better is to sign the procedure with a certificate and create a
user from the certificate that has the required rights.
I have a longer article on my web site that discusses EXECUTE AS and
procedure-signing in detail: http://www.sommarskog.se/grantperm.html.
--
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

[Erland Sommarskog]

Post by unknown
What we have is an internet app that has an sql login i_user that can
only execute stored procedures, no select statements or anything else.
But some stored procedures and triggers use dynamic sql and so I put
with the with execute as 'dbo' statement in those so the applications
i_user login can execute those. I will use your article to redo this.
Again, thanks.

The main reason I wrote that article was to tell people not to use
EXECUTE AS 'dbo' to handle permissions and dynamic SQL, but show that
there are better ways around it. I hope you find the article valuable.