Re: SQL 2005 public role

You don't need REVERT when you create a proc with the EXECUTE AS option.

--
Tom

----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"Uri Dimant" <urid@xxxxxxxxxxx> wrote in message
news:u537sgmUGHA.5044@xxxxxxxxxxxxxxxxxxxxxxx
Tom
Yep ( I have just forgotten that he uses SQL Server 2005) , however I still
think it will be better to GRANT EXECUTE permissions rather using EXECUTE
AS command , (it is easy to forget REVERT and ect. )



"Tom Moreau" <tom@xxxxxxxxxxxxxxxxxxxx> wrote in message
news:ug9ojcmUGHA.3192@xxxxxxxxxxxxxxxxxxxxxxx
He can use EXECUTE AS in order to avoid granting permissions on the
underlying tables.

--
Tom

----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Uri Dimant" <urid@xxxxxxxxxxx> wrote in message
news:%23D52SXmUGHA.4660@xxxxxxxxxxxxxxxxxxxxxxx
Hi
Who is an owner of this SP?
Avoid adding logins to the public role. Grant an execution permission to
the
stored procedure ( be aware if you are using dynamic sql within this sp ,
you'll have to grant permissions on underlying tables as well)



"Franky" <Franky@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:37C732BA-FAD2-43F8-A0EF-1F23383674B8@xxxxxxxxxxxxxxxx
I've made a stored procedure stp_LogError which will be used to have a
central errorhandling system.
(So in the usermade stored procedures in the catch-block stp_Logerror
will
be used to log the error).
I've granted execute permissions for stp_LogError to the public role.
Afterward I added a NT-login to the server and added this login to the
role
public in the database.
I granted this user execute rights to a stored procedure in which
stp_Logerror is used.
When this user calls this stored procedure, this stored procedure
executes,
but when an error occurs we receive an execute permission denied on
stp_LogError for this user.

When I check the login for this user it is made a user in the database
through the role public.
When I check the role public in the database the user is not a member of
the role public.

I thaught that every user which is granted acces to a database is member
of
the public role, so when i granted execute rights for stp_logerror to the
public role, everyone (except the explicitly denied ones) would have
execute
rights on stp_LogError.

Can someone explain this to me




.

Relevant Pages

  • Re: Stored Procedure Security/Permissions
    ... Cross-Database Ownership Chaining Behavior Changes in SQL Server 2000 ... > permission to read either table; ... > permission to execute the stored procedure, ...
    (microsoft.public.sqlserver.security)
  • Re: SQL 2005 public role
    ... Grant an execution permission to the ... stored procedure (be aware if you are using dynamic sql within this sp, ... I've granted execute permissions for stp_LogError to the public role. ...
    (microsoft.public.sqlserver.security)
  • Connection.Execute and SQL stored procedures
    ... ..asp, the connection object and MS SQL Server as the DB can help me out. ... Five of the six calls execute as expected. ... This particular stored procedure does quite a bit of work within the DB, ...
    (microsoft.public.sqlserver.odbc)
  • Re: delete some user from the public role
    ... really my problem is related with a DTS execution. ... and when I try to execute the DTS from the ... I don't like to take out this 'denied' to the public role, ... > Jasper Smith (SQL Server MVP) ...
    (microsoft.public.sqlserver.security)
  • Re: ADO.NET Timout
    ... That in turn execute SP ... BeginExecute method of the Command object. ... How can I call a stored procedure to do its thing asynchronously. ... see "Explicit Transactions" in SQL Server Books ...
    (microsoft.public.dotnet.framework.adonet)