Re: Find Database Users

From: Uri Dimant (urid_at_iscar.co.il)
Date: 01/09/05

  • Next message: DB: "Re: How to access SQL Server on another domain with Windows authentica" 
    Date: Sun, 9 Jan 2005 10:00:00 +0200

    Ann
    Aaron Bertrand wrote
    This will only cover those who have
    been explicitly given access to the procs using GRANT.

    DECLARE @username VARCHAR(32)
    SELECT @username = '???'
    SELECT o.name, CanExec = CASE WHEN p.id = o.id THEN 'Yes' ELSE 'No' END
     FROM sysobjects o LEFT OUTER JOIN syspermissions p
     ON o.id = p.id
    AND p.grantee = USER_ID(@username)

    "Ann" <ao_949@hotmail.com> wrote in message
    news:127901c4f519$8e655690$a401280a@phx.gbl...
    > I'm wondering if there is a way I can query against a
    > system table to find out all the users on a server and
    > what permissions they all have? If anyone has any ideas I
    > would really appreciate it! Thanks!
    >

  • Next message: DB: "Re: How to access SQL Server on another domain with Windows authentica"

    Relevant Pages

    • Re: Table permissions
      ... This script was written by Aaron Bertrand ... been explicitly given access to the procs using GRANT. ... SELECT @username = '???' ...
      (microsoft.public.sqlserver.programming)
    • Re: Retrieving ALL Object Privs for ALL Users in ALL Databases
      ... been explicitly given access to the procs using GRANT. ... SELECT @username = '???' ... FROM sysobjects o LEFT OUTER JOIN syspermissions p ... > Does someone have a procedure that will loop through ALL databases and get ...
      (microsoft.public.sqlserver.security)
    • Re: Adding users
      ... Not only does AUTHORIZE not show it, new processes did not get the right ... When I grant the id I get the GRANTMSG returned, but SHOW USERNAME does ... A second grant gives me a GRANTERR ...
      (comp.os.vms)
    • Re: Group policy software install & directory access rights
      ... Grant "Authenticated Users" read/execute access. ... > the install files if it does not have a username and password? ... > So I log into the local workstation as the local admin which is not a ... > So I'm thinking that the reason my GPO install is failing is because the ...
      (microsoft.public.windows.server.active_directory)
    • Re: OBJECT Level Permissions
      ... Pro SQL Server 2000 Database Design ... > There isn't a generic collection object that would allow you to GRANT EXEC ... > on all procs in a single statement. ... > However you could build a cursor that looks at sysobjects and loops though ...
      (microsoft.public.sqlserver.programming)