Re: What commands can a user execute

Thanks. This is helpful - but I'm still missing something. When executing
the query - yes, I'm aware of it - either at the Server or Database scope, no
where do I find the permission for DROP TABLE or DROP DATABASE but I do find
CREATE TABLE and CREATE DATABASE. Another example would be the command
UPDATE STATISTICS. BOL states you need ALTER permissions on the table or
view. OK, understood. So, how do I query the system to tell me all the
commands a specific user can execute without referencing BOL for every
command ? Incidently, this is a question my manager asked and auditors are
expected to ask the same. I thought it would be fairly easy to compile a
full list of SQL commands that a user can execute but, so far, it has not.
Any help would be appreciated. Thanks again. -CqlBoy

"Erland Sommarskog" wrote:

Cqlboy (Cqlboy@xxxxxxxxxxxxxxxxxxxxxxxxx) writes:
I have user access SQL Server 2005 via a Windows NT Group which is not
mapped to any server roles but is a db_owner in almost all databases. I
just learned from a security monitoring script which logs security
events that a member from this NT Group created and dropped a database.
How ??? Inspecting sp_srvrolepermission and sp_dbfixedrolepermission I
find that only the dbcreation server role has the permission to
create/drop a database which, again, my NT Group is not mapped to. How
do I determine an NT Groups permissions and more importantly, which
commands they can execute ? Thanks. -CqlBoy

Do

EXECUTE AS LOGIN = 'GROUP\USER'
go
SELECT * FROM fn_my_permissions(NULL, 'server')
go
REVERT


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

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx


.

Relevant Pages

  • RE: Backups have Shadow Copy Problems
    ... and restarted the server. ... suggested and changed the recovery model to simple on the one database called ... I understand the issue to be: the backup task failed ... You back up data from a volume that contains a Microsoft SQL Server ...
    (microsoft.public.windows.server.sbs)
  • Re: upsizing to sql 2005
    ... the word SERVER in it, ... You can access to the database by multiple means (Access, ... and how does it update the SQL database with the new records in Access? ... Query Name: Arcadia - ARC ...
    (microsoft.public.access.queries)
  • Re: Linked Tables in Access
    ... any use of SQL Passthru, Linked Tables or any other use of MDB / MDE ... server, or would I also need to convert *those* queries to passthrough ... I've been trying to understand why Access database files become corrupt. ...
    (microsoft.public.access.adp.sqlserver)
  • Re: Leveling by ID vs. "Standard"
    ... of this trade called Project Management. ... a database for the "Project Tables," ... to write reports on data from the server database. ... product supporting queries created with SQL. ...
    (microsoft.public.project)
  • Re: Trouble Getting VS.Net 2003 WalkThroughs MSDE Connection
    ... Config Tool of SQL Server? ... > link to download the PUBs database. ... >>> Setup and they directed me to install MSDE and they attached a ...
    (microsoft.public.sqlserver.msde)
Loading