Re: user permissions

Let's look at the problem again.

A user should be able to kill connections only for a specific database.

We've come up with the solution to make a stored procedure that is checking
the connections dbid, before killing the connection.

1. About the security we could do an EXECUTE AS LOGIN in the stored
procedure. We then have to grant IMPERSONATE rights.

The problem with this is that the user could easily bypass the logic in the
stored procedure, by doing an EXECUTE AS LOGIN and then kill any connection.

2. We could also create the stored procedure WITH EXECUTE AS OWNER and mark
the database as TRUSTWORTHY. You're right that you have to be very careful
with this. One solution could be to create the stored procedure in the master
database (or a custom admin datatabase) and mark that database as TRUSTWORTHY.

Ola Hallengren
http://ola.hallengren.com



"Charles Wang[MSFT]" wrote:

Yes, that is also an option for impersonation. Just one thing I want to
point out since TRUSTWORTHY is at database level, it may impact other UDFs
and SPs who do not need an impersonation context. EXECUTE AS clause is
still the first priority here.

Best regards,
Charles Wang
Microsoft Online Community Support
=========================================================
Delighting our customers is our #1 priority. We welcome your
comments and suggestions about how we can improve the
support we provide to you. Please feel free to let my manager
know what you think of the level of service provided. You can
send feedback directly to my manager at: msdnmg@xxxxxxxxxxxxxx
=========================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
=========================================================



.

Relevant Pages

  • Re: Execute stored procedure only
    ... You need CONNECT capability to be able to get into the database and execute ... You can compare this to having permission to eat a piece of pie, ... Should I not be able to just execute SP through QA? ... right to execute stored procedure in database called "mydatabase". ...
    (microsoft.public.sqlserver.security)
  • Re: Connections query
    ... Execute method against the command. ... this bit of code opens up two connections. ... Firstly select @@connections returns +2 on my previous call to it. ... stored procedure than the first recordset. ...
    (microsoft.public.sqlserver.programming)
  • Could not find stored procedure error
    ... I can execute a select query on a database but ... I get an error when I try to execute a stored procedure. ... database which is preventing it from finding my stored procedures. ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Stored Prcedure Security
    ... There is a section in BOL regarding cross database ownership chains and the ... > permission to read either table; ... > permission to execute the stored procedure, ...
    (microsoft.public.sqlserver.programming)
  • Execute Stored Procedure from SharePoint
    ... SharePoint) from within a SharePoint page. ... non-SharePoint database and execute a stored procedure wihtout MS ...
    (microsoft.public.sharepoint.portalserver.development)