Re: user permissions

Ola Hallengren (OlaHallengren@xxxxxxxxxxxxxxxxxxxxxxxxx) writes:
A user should be able to kill connections only for a specific database.

And in the end the user who is causing he mess in database B may have
database A as his current database, and is now running a cross-database
query...

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.

Or sign the procedure with a certificate, and create a login from this
certificate, and add this login to the processadmin server role. Note
that this login is not actually able to login, it's just a connection
between the certificate and the permission.

There should be no need for making things trustworthy here.

--
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: System Administrator Implied Permissions
    ... > sa login, it assigns it the System Administrator fixed ... > Now, given this, why does SQL Server ... in each database is always a member of the public and db_owner roles. ... Other sysadmin role members have the exact same ...
    (microsoft.public.sqlserver.security)
  • 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: cannot login to the db after...
    ... Jasper Smith (SQL Server MVP) ... I have created a new database, "db_1", using the "sa" ... I then created a new login, "sqluser1" and gave ...
    (microsoft.public.sqlserver.security)
  • Re: Cant view merge agent properties (trying again)
    ... In the List of Actions for the Snapshot Agent History I see this repeated: ... every single database listed. ... So, just now, I went to computername\Administrator Login ID (because it's ... On the computer running SQL Server, ...
    (microsoft.public.sqlserver.replication)
  • Re: Database security design with ASP.net and form-based authentication
    ... Since you already have forms-based security, why not use a single SQL login ... for all database access? ... data entry, guest/view only, admin, report viewer. ... so I'm using SQL Server authentication. ...
    (microsoft.public.sqlserver.security)