locked
Query to find objects (Databases, Tables, Stored Procs, SQL Jobs, Views etc) owned by a given user? RRS feed

  • Question

  • In SQL Server 2000, how to find objects (Databases, Tables, Stored Procs, SQL Jobs, Views etc) owned by a given user?

    It is required for checking and changing object ownership before delation.
    Wednesday, September 24, 2008 6:21 PM

Answers


  • Got the solution:

    sp_Msforeachdb "USE [?];select DB_Name= db_name(),  * from sysobjects where uid = user_id('barnetp')"

    where
    barnetp is the user name.

    this script will check object owned by the given user against all the database
    Wednesday, September 24, 2008 6:39 PM

All replies


  • Got the solution:

    sp_Msforeachdb "USE [?];select DB_Name= db_name(),  * from sysobjects where uid = user_id('barnetp')"

    where
    barnetp is the user name.

    this script will check object owned by the given user against all the database
    Wednesday, September 24, 2008 6:39 PM
  • A quick word of advice. sp_MSforeachdb is undocumented and we strongly recommend against using it. Since this SP is not supported it may be subject to change its behavior in a future release (not likely to happen, but something you must consider) and be careful when using it as it executes the code sent as parameter, which may be subject to SQL injections (to be more specific, may be subject to second order injection attacks via the DB name).

     

      Please notice that Sivaprasad's code is escaping the question mark (used as a place holder for the DB) using the syntax "[?]", this means that the db name is going to be internally escaped using "[]" to prevent injection on the USE statement.

     

    -Raul Garcia

      SDE/T

      SQL Server Engine

     

    Wednesday, September 24, 2008 7:02 PM