Find all objects in Server Owned by a Login

  • RBarryYoung

    SSC Guru

    Points: 143327

    Comments posted to this topic are about the item Find all objects in Server Owned by a Login

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.
    [/font]
    [font="Verdana"] "Performance is our middle name."[/font]

  • Mark D Powell

    SSCarpal Tunnel

    Points: 4450

    I get errors when I try to run the code using Standard Edition with SP2 applied.

    SELECT * FROM (Select 1 as DBID,

    'master' as DBName, L.name as Login, U.Name as [User], O.*

    From master.sys.objects o

    Join master.sys.database_principals u

    ON Coalesce(o.principal_id,

    (Select S.Principal_ID from master.sys.schemas S Where S.Schema_ID = O.schema_id))

    = U.principal_id

    left join master.sys.server_principals L on L.sid = u.sid UNION ALL Select 11, 'E4BOTTLELABEL', L.name as Login, U.Name as [User], O.*

    From E4BOTTLELABEL.sys.objects o

    Join E4BOTTLELABEL.sys.database_principals u

    ON Coalesce(o.principal_id,

    (Select S.Principal_ID from E4BOTTLELABEL.sys.schemas S Where S.Schema_ID = O.schema_id))

    = U.principal_id

    left join E4BOTTLELABEL.sys.server_principals L on L.sid = u.sid UNION ALL Select 7, 'E4COMMON', L.name as Login, U.Name as [User], O.*

    From E4COMMON.sys.objects o

    Join E4COMMON.sys.database_principals u

    ON Coalesce(o.principal_id,

    (Select S.Principal_ID from E4COMMON.sys.schemas S Where S.Schema_ID = O.schema_id))

    = U.principal_id

    left join E4COMMON.sys.server_principals L on L.sid = u.sid UNION ALL Select 8, 'E4ENGINECERT', L.name as Login, U.Name as [User], O.*

    From E4ENGINECERT.sys.objects o

    Join E4ENGINECERT.sys.database_principals u

    ON Coalesce(o.principal_id,

    (Select S.Principal_ID from E4ENGINECERT.sys.schemas S Where S.Schema_ID = O.schema_id))

    = U.principal_id

    left join E4ENGINECERT.sys.server_principals L on L.sid = u.sid UNION ALL Select 9, 'E4ENGINEUTIL', L.name as Login, U.Name as [User], O.*

    From E4ENGINEUTIL.sys.objects o

    Join E4ENGINEUTIL.sys.database_principals u

    ON Coalesce(o.principal_id,

    (Select S.Principal_ID from E4ENGINEUTIL.sys.schemas S Where S.Schema_ID = O.schema_id))

    = U.principal_id

    left join E4ENGINEUTIL.sys.server_principals L on L.sid = u.sid UNION ALL Select 10, 'E4TESTREQUEST', L.name as Login, U.Name as [User], O.*

    From E4TESTREQUEST.sys.objects o

    Join E4TESTREQUEST.sys.database_principals u

    ON Coalesce(o.principal_id,

    (Select S.Principal_ID from E4TESTREQUEST.sys.schemas S Where S.Schema_ID = O.schema_id))

    = U.principal_id

    left join E4TESTREQUEST.sys.server_principals L on L.sid = u.sid UNION ALL Select 12, 'ELAB', L.name as Login, U.Name as [User], O.*

    From ELAB.sys.objects o

    Join ELAB.sys.database_principals u

    ON Coalesce(o.principal_id,

    (Select S.Principal_ID from ELAB.sys.schemas S Where S.Schema_ID = O.schema_id))

    = U.principal_id

    left join ELAB.sys.server_principals L on L.sid = u.sid UNION ALL Select 3, 'model', L.name as Login, U.Name as [User], O.*

    From model.sys.objects o

    Join model.sys.database_principals u

    ON Coalesce(o.principal_id,

    (Select S.Principal_ID from model.sys.schemas S Where S.Schema_ID = O.schema_id))

    = U.principal_id

    left join model.sys.server_principals L on L.sid = u.sid UNION ALL Select 4, 'msdb', L.name as Login, U.Name as [User], O.*

    From msdb.sys.objects o

    Join msdb.sys.database_principals u

    ON Coalesce(o.principal_id,

    (Select S.Principal_ID from msdb.sys.schemas S Where S.Schema_ID = O.schema_id))

    = U.principal_id

    left join msdb.sys.server_principals L on L.sid = u.sid UNION ALL Select 5, 'ReportServer', L.name as Login, U.Name as [User], O.*

    From ReportServer.sys.objects o

    Join ReportServer.sys.database_principals u

    ON Coalesce(o.principal_id,

    (Select S.Principal_ID from ReportServer.sys.schemas S Where S.Schema_ID = O.schema_id))

    = U.principal_id

    left join ReportServer.sys.server_principals L on L.sid = u.sid UNION ALL Select 6, 'ReportServerTempDB', L.name as Login, U.Name as [User], O.*

    From ReportServerTempDB.sys.objects o

    Join ReportServerTempDB.sys.database_principals u

    ON Coalesce(o.principal_id,

    (Select S.Principal_ID from ReportServerTempDB.sys.schemas S Where S.Schema_ID = O.schema_id))

    = U.principal_id

    left join ReportServerTempDB.sys.server_principals L on L.sid = u.sid UNION ALL Select 2, 'tempdb', L.name as Login, U.Name as [User], O.*

    From tempdb.sys.objects o

    Join tempdb.sys.database_principals u

    ON Coalesce(o.principal_id,

    (Select S.Principal_ID from tempdb.sys.schemas S Where S.Schema_ID = O.schema_id))

    = U.principal_id

    left join tempdb.sys.server_principals L on L.sid = u.sid ) oo Where Login = 'sa'

    Msg 451, Level 16, State 1, Line 1

    Cannot resolve collation conflict for column 4 in SELECT statement.

    Msg 451, Level 16, State 1, Line 1

    Cannot resolve collation conflict for column 5 in SELECT statement.

    I was going to try expaning the O.* column list but collation conflict seems like it could be indicating a different problem that resolving the table names correctly.

  • RBarryYoung

    SSC Guru

    Points: 143327

    Hmm, thats a new one on me. Of course I hardly ever get to work with DB's with different collations.

    Try this altered version and let me know if it works:

    Alter Proc spLogin_OwnedObjects ( @Login as SYSNAME ) As

    /*

    Display all objects in all DBs owned by the Login.

    2008-07-06 RBarryYoung Created.

    Test:

    spLogin_OwnedObjects 'sa'

    */

    declare @sql varchar(MAX), @DB_Objects varchar(512)

    Select @DB_Objects = ' L.name COLLATE DATABASE_DEFAULT as Login, U.Name COLLATE DATABASE_DEFAULT as [User], O.*

    From %D%.sys.objects o

    Join %D%.sys.database_principals u

    ON Coalesce(o.principal_id, (Select S.Principal_ID from %D%.sys.schemas S Where S.Schema_ID = O.schema_id))

    = U.principal_id

    left join %D%.sys.server_principals L on L.sid = u.sid

    '

    Select @sql = 'SELECT * FROM

    (Select '+Cast(database_id as varchar(9))+' as DBID, ''master'' as DBName, '

    + Replace(@DB_objects, '%D%', [name])

    From master.sys.databases

    Where [name] = 'master'

    Select @sql = @sql + '

    UNION ALL Select '+Cast(database_id as varchar(9))+', '''+[name]+''', '

    + Replace(@DB_objects, '%D%', [name])

    From master.sys.databases

    Where [name] != 'master'

    Select @sql = @sql + ') oo Where Login = ''' + @Login + ''''

    print @sql

    EXEC (@sql)

    If you still get the second error, try taking out the "o.*" and see if it will run without error (if so, then I will need to make a large change).

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.
    [/font]
    [font="Verdana"] "Performance is our middle name."[/font]

  • Mark D Powell

    SSCarpal Tunnel

    Points: 4450

    All my databases are SQL_Latin1_General_CP1_CI_AS which as far as I know was the SQL Server 2000 default.

    I am pretty sure that when I installed SQL Server 2005 that I took the defaults. The ReportServer databases have a collation of Latin1_General_CI_AS_KS_WS.

    You fix still errored off so what I did was to add and [name] != 'ReportServer' and [name] != 'ReportServerTempDB' to the second union's query.

    That seems to have gotten around the issue.

    Anyone know if the ReportServer settings are normal?

    -- Mark D Powell --

  • RBarryYoung

    SSC Guru

    Points: 143327

    Yep, they are that way on my SQL 2008 box also.

    Well here is a corrected and tested version that should work for all of the databases, including the Reporting DBs:

    ALTER Proc spLogin_OwnedObjects ( @Login as SYSNAME ) As

    /*

    Display all objects in all DBs owned by the Login.

    2008-07-06 RBarryYoung Created.

    Test:

    EXEC spLogin_OwnedObjects 'sa'

    */

    declare @sql varchar(MAX), @DB_Objects varchar(MAX)

    Select @DB_Objects = ' L.name COLLATE DATABASE_DEFAULT as Login, U.Name COLLATE DATABASE_DEFAULT as [User]

    , o.name COLLATE DATABASE_DEFAULT as [name]

    , o.object_id

    , o.principal_id

    , o.schema_id

    , o.parent_object_id

    , o.type COLLATE DATABASE_DEFAULT as [type]

    , o.type_desc COLLATE DATABASE_DEFAULT as [type_desc]

    , o.create_date

    , o.modify_date

    , o.is_ms_shipped

    , o.is_published

    , o.is_schema_published

    From %D%.sys.objects o

    Join %D%.sys.database_principals u

    ON Coalesce(o.principal_id

    , (Select S.Principal_ID from %D%.sys.schemas S Where S.Schema_ID = O.schema_id))

    = U.principal_id

    Left Join %D%.sys.server_principals L on L.sid = u.sid

    '

    Select @sql = 'SELECT * FROM

    (Select '+Cast(database_id as varchar(9))+' as DBID, ''master'' as DBName, '

    + Replace(@DB_objects, '%D%', [name])

    From master.sys.databases

    Where [name] = 'master'

    Select @sql = @sql + 'UNION ALL Select '+Cast(database_id as varchar(9))+', '''+[name]+''', '

    + Replace(@DB_objects, '%D%', [name])

    From master.sys.databases

    Where [name] != 'master'

    Select @sql = @sql + ') oo Where Login = ''' + @Login + ''''

    print @sql

    EXEC (@sql)

    Thanks for pointing this out to me Mark. I will get the corrections up to the article ASAP.

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.
    [/font]
    [font="Verdana"] "Performance is our middle name."[/font]

  • Lucien Grieten

    Valued Member

    Points: 60

    Hello, I am a newbie to sql server and I have created the sp in Master

    When I execute the sp using 'sa' as parameter the following error occurs: Can you help?

    SELECT * FROM

    (Select 1 as DBID, 'master' as DBName, L.name COLLATE DATABASE_DEFAULT as Login, U.Name COLLATE DATABASE_DEFAULT as [User]

    , o.name COLLATE DATABASE_DEFAULT as [name]

    , o.object_id

    , o.principal_id

    , o.schema_id

    , o.parent_object_id

    , o.type COLLATE DATABASE_DEFAULT as [type]

    , o.type_desc COLLATE DATABASE_DEFAULT as [type_desc]

    , o.create_date

    , o.modify_date

    , o.is_ms_shipped

    , o.is_published

    , o.is_schema_published

    From master.sys.objects o

    Join master.sys.database_principals u

    ON Coalesce(o.principal_id

    , (Select S.Principal_ID from master.sys.schemas S Where S.Schema_ID = O.schema_id))

    = U.principal_id

    Left Join master.sys.server_principals L on L.sid = u.sid

    UNION ALL Select 7, 'APS CRP PS', L.name COLLATE DATABASE_DEFAULT as Login, U.Name COLLATE DATABASE_DEFAULT as [User]

    , o.name COLLATE DATABASE_DEFAULT as [name]

    , o.object_id

    , o.principal_id

    , o.schema_id

    , o.parent_object_id

    , o.type COLLATE DATABASE_DEFAULT as [type]

    , o.type_desc COLLATE DATABASE_DEFAULT as [type_desc]

    , o.create_date

    , o.modify_date

    , o.is_ms_shipped

    , o.is_published

    , o.is_schema_published

    From APS CRP PS.sys.objects o

    Join APS CRP PS.sys.database_principals u

    ON Coalesce(o.principal_id

    , (Select S.Principal_ID from APS CRP PS.sys.schemas S Where S.Schema_ID = O.schema_id))

    = U.principal_id

    Left Join APS CRP PS.sys.server_principals L on L.sid = u.sid

    UNION ALL Select 8, 'APS CRP PS TEST', L.name COLLATE DATABASE_DEFAULT as Login, U.Name COLLATE DATABASE_DEFAULT as [User]

    , o.name COLLATE DATABASE_DEFAULT as [name]

    , o.object_id

    , o.principal_id

    , o.schema_id

    , o.parent_object_id

    , o.type COLLATE DATABASE_DEFAULT as [type]

    , o.type_desc COLLATE DATABASE_DEFAULT as [type_desc]

    , o.create_date

    , o.modify_date

    , o.is_ms_shipped

    , o.is_published

    , o.is_schema_published

    From APS CRP PS TEST.sys.objects o

    Join APS CRP PS TEST.sys.database_principals u

    ON Coalesce(o.principal_id

    , (Select S.Principal_ID from APS CRP PS TEST.sys.schemas S Where S.Schema_ID = O.schema_id))

    = U.principal_id

    Left Join APS CRP PS TEST.sys.server_principals L on L.sid = u.sid

    UNION ALL Select 9, 'APS Shortterm planning', L.name COLLATE DATABASE_DEFAULT as Login, U.Name COLLATE DATABASE_DEFAULT as [User]

    , o.name COLLATE DATABASE_DEFAULT as [name]

    , o.object_id

    , o.principal_id

    , o.schema_id

    , o.parent_object_id

    , o.type COLLATE DATABASE_DEFAULT as [type]

    , o.type_desc COLLATE DATABASE_DEFAULT as [type_desc]

    , o.create_date

    , o.modify_date

    , o.is_ms_shipped

    , o.is_published

    , o.is_schema_published

    From APS Shortterm planning.sys.objects o

    Join APS Shortterm planning.sys.database_principals u

    ON Coalesce(o.principal_id

    , (Select S.Principal_ID from APS Shortterm planning.sys.schemas S Where S.Schema_ID = O.schema_id))

    = U.principal_id

    Left Join APS Shortterm planning.sys.server_principals L on L.sid = u.sid

    UNION ALL Select 3, 'model', L.name COLLATE DATABASE_DEFAULT as Login, U.Name COLLATE DATABASE_DEFAULT as [User]

    , o.name COLLATE DATABASE_DEFAULT as [name]

    , o.object_id

    , o.principal_id

    , o.schema_id

    , o.parent_object_id

    , o.type COLLATE DATABASE_DEFAULT as [type]

    , o.type_desc COLLATE DATABASE_DEFAULT as [type_desc]

    , o.create_date

    , o.modify_date

    , o.is_ms_shipped

    , o.is_published

    , o.is_schema_published

    From model.sys.objects o

    Join model.sys.database_principals u

    ON Coalesce(o.principal_id

    , (Select S.Principal_ID from model.sys.schemas S Where S.Schema_ID = O.schema_id))

    = U.principal_id

    Left Join model.sys.server_principals L on L.sid = u.sid

    UNION ALL Select 4, 'msdb', L.name COLLATE DATABASE_DEFAULT as Login, U.Name COLLATE DATABASE_DEFAULT as [User]

    , o.name COLLATE DATABASE_DEFAULT as [name]

    , o.object_id

    , o.principal_id

    , o.schema_id

    , o.parent_object_id

    , o.type COLLATE DATABASE_DEFAULT as [type]

    , o.type_desc COLLATE DATABASE_DEFAULT as [type_desc]

    , o.create_date

    , o.modify_date

    , o.is_ms_shipped

    , o.is_published

    , o.is_schema_published

    From msdb.sys.objects o

    Join msdb.sys.database_principals u

    ON Coalesce(o.principal_id

    , (Select S.Principal_ID from msdb.sys.schemas S Where S.Schema_ID = O.schema_id))

    = U.principal_id

    Left Join msdb.sys.server_principals L on L.sid = u.sid

    UNION ALL Select 6, 'MWareArc', L.name COLLATE DATABASE_DEFAULT as Login, U.Name COLLATE DATABASE_DEFAULT as [User]

    , o.name COLLATE DATABASE_DEFAULT as [name]

    , o.object_id

    , o.principal_id

    , o.schema_id

    , o.parent_object_id

    , o.type COLLATE DATABASE_DEFAULT as [type]

    , o.type_desc COLLATE DATABASE_DEFAULT as [type_desc]

    , o.create_date

    , o.modify_date

    , o.is_ms_shipped

    , o.is_published

    , o.is_schema_published

    From MWareArc.sys.objects o

    Join MWareArc.sys.database_principals u

    ON Coalesce(o.principal_id

    , (Select S.Principal_ID from MWareArc.sys.schemas S Where S.Schema_ID = O.schema_id))

    = U.principal_id

    Left Join MWareArc.sys.server_principals L on L.sid = u.sid

    UNION ALL Select 5, 'MWarePrd', L.name COLLATE DATABASE_DEFAULT as Login, U.Name COLLATE DATABASE_DEFAULT as [User]

    , o.name COLLATE DATABASE_DEFAULT as [name]

    , o.object_id

    , o.principal_id

    , o.schema_id

    , o.parent_object_id

    , o.type COLLATE DATABASE_DEFAULT as [type]

    , o.type_desc COLLATE DATABASE_DEFAULT as [type_desc]

    , o.create_date

    , o.modify_date

    , o.is_ms_shipped

    , o.is_published

    , o.is_schema_published

    From MWarePrd.sys.objects o

    Join MWarePrd.sys.database_principals u

    ON Coalesce(o.principal_id

    , (Select S.Principal_ID from MWarePrd.sys.schemas S Where S.Schema_ID = O.schema_id))

    = U.principal_id

    Left Join MWarePrd.sys.server_principals L on L.sid = u.sid

    UNION ALL Select 2, 'tempdb', L.name COLLATE DATABASE_DEFAULT as Login, U.Name COLLATE DATABASE_DEFAULT as [User]

    , o.name COLLATE DATABASE_DEFAULT as [name]

    , o.object_id

    , o.principal_id

    , o.schema_id

    , o.parent_object_id

    , o.type COLLATE DATABASE_DEFAULT as [type]

    , o.type_desc COLLATE DATABASE_DEFAULT as [type_desc]

    , o.create_date

    , o.modify_date

    , o.is_ms_shipped

    , o.is_published

    , o.is_schema_published

    From tempdb.sys.objects o

    Join tempdb.sys.database_principals u

    ON Coalesce(o.principal_id

    , (Select S.Principal_ID from tempdb.sys.schemas S Where S.Schema_ID = O.schema_id))

    = U.principal_id

    Left Join tempdb.sys.server_principals L on L.sid = u.sid

    ) oo Where Login = 'sa'

    Msg 102, Level 15, State 1, Line 34

    Incorrect syntax near 'PS'.

    Msg 102, Level 15, State 1, Line 37

    Incorrect syntax near 'PS'.

    Msg 102, Level 15, State 1, Line 53

    Incorrect syntax near 'PS'.

    Msg 102, Level 15, State 1, Line 56

    Incorrect syntax near 'PS'.

    Msg 102, Level 15, State 1, Line 72

    Incorrect syntax near 'planning'.

    Msg 102, Level 15, State 1, Line 75

    Incorrect syntax near 'planning'.

    Msg 102, Level 15, State 1, Line 173

    Incorrect syntax near ')'.

  • RBarryYoung

    SSC Guru

    Points: 143327

    Looks like you have spaces in your database names, something that I did not take into account. Try this new version and let me know if it works.

    Alter Proc spLogin_OwnedObjects ( @Login as SYSNAME ) As

    /*

    Display all objects in all DBs owned by the Login.

    2008-07-06 RBarryYoung Created.

    2008-08-28 RBarryYoung Corrected of Windows vs SS default collations.

    2008-12-06 RBarryYoung Fixed for spaces in DB names.

    Test:

    spLogin_OwnedObjects 'sa'

    */

    declare @sql varchar(MAX), @DB_Objects varchar(512)

    Select @DB_Objects = ' L.name COLLATE DATABASE_DEFAULT as Login, U.Name COLLATE DATABASE_DEFAULT as [User], O.*

    From [%D%].sys.objects o

    Join [%D%].sys.database_principals u

    ON Coalesce(o.principal_id, (Select S.Principal_ID from [%D%].sys.schemas S Where S.Schema_ID = O.schema_id))

    = U.principal_id

    left join [%D%].sys.server_principals L on L.sid = u.sid

    '

    Select @sql = 'SELECT * FROM

    (Select '+Cast(database_id as varchar(9))+' as DBID, ''master'' as DBName, '

    + Replace(@DB_objects, '%D%', [name])

    From master.sys.databases

    Where [name] = 'master'

    Select @sql = @sql + '

    UNION ALL Select '+Cast(database_id as varchar(9))+', '''+[name]+''', '

    + Replace(@DB_objects, '%D%', [name])

    From master.sys.databases

    Where [name] != 'master'

    Select @sql = @sql + ') oo Where Login = ''' + @Login + ''''

    print @sql

    EXEC (@sql)

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.
    [/font]
    [font="Verdana"] "Performance is our middle name."[/font]

  • Lucien Grieten

    Valued Member

    Points: 60

    Thanks very much, this is working fine now, really appreciate your answer

    Lucien

  • RBarryYoung

    SSC Guru

    Points: 143327

    Glad I could help.

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.
    [/font]
    [font="Verdana"] "Performance is our middle name."[/font]

  • tbanks-1094621

    SSC Rookie

    Points: 48

    Hope you don't mind, I found the guts of this quite useful but modified it in a couple of ways:

    1. First, I've got enough databases on the server in question that I was running into a 256-table union limit while attempting to run it the way it was.

    2. Secondly, I'd like on occasion to see all owners of all objects. A null @Login will return this result.

    Alter Proc spLogin_OwnedObjects ( @Login as SYSNAME ) As

    /*

    Display all objects in all DBs owned by the Login.

    2008-07-06 RBarryYoung Created.

    2008-08-28 RBarryYoung Corrected of Windows vs SS default collations.

    2008-12-06 RBarryYoung Fixed for spaces in DB names.

    2009-06-01 TBanks Fixed for large quantities of DBs to avoid the 256-table union limit

    Test:

    spLogin_OwnedObjects 'sa'

    */

    set nocount on

    create table ##objectowners(

    [DBID] int,

    DBName varchar(255),

    [Login] varchar(255),

    [User] varchar(255),

    name varchar(255),

    [object_id] [int],

    [principal_id] [int],

    [schema_id] [int],

    [parent_object_id] [int],

    [type] [char](2),

    [type_desc] [nvarchar](60),

    [create_date] [datetime],

    [modify_date] [datetime],

    [is_ms_shipped] [bit],

    [is_published] [bit],

    [is_schema_published] [bit])

    declare @sql varchar(MAX),

    @DB_Objects varchar(512)

    Select @DB_Objects = ' L.name COLLATE DATABASE_DEFAULT as Login, U.Name COLLATE DATABASE_DEFAULT as [User], O.*

    From [%D%].sys.objects o

    Join [%D%].sys.database_principals u

    ON Coalesce(o.principal_id, (Select S.Principal_ID from [%D%].sys.schemas S Where S.Schema_ID = O.schema_id))

    = U.principal_id

    left join [%D%].sys.server_principals L on L.sid = u.sid

    '

    Select @sql = 'insert ##objectowners SELECT * FROM

    (Select '+Cast(database_id as varchar(9))+' as DBID, ''master'' as DBName, '

    + Replace(@DB_objects, '%D%', [name])

    From master.sys.databases

    Where [name] = 'master'

    select @sql = @sql + case when @Login is null then ') oo'

    else ') oo Where Login = ''' + @Login + ''''

    end

    EXEC (@sql)

    declare @db varchar(100),

    @db_id int

    DECLARE db_cursor CURSOR FOR

    SELECT name, database_id

    FROM master.sys.databases

    where name 'master'

    OPEN db_cursor

    FETCH NEXT FROM db_cursor INTO @db, @db_id

    WHILE @@FETCH_STATUS = 0

    BEGIN

    SELECT @sql = 'insert ##objectowners Select '+Cast(@db_id as varchar(9))+', '''+@db+''', '

    + Replace(@DB_objects, '%D%', @db)

    select @sql = @sql + case when @Login is null then ' '

    else ' Where Login = ''' + @Login + ''''

    end

    exec (@sql)

    FETCH NEXT FROM db_cursor INTO @db, @db_id

    END

    CLOSE db_cursor

    DEALLOCATE db_cursor

    select * from ##objectowners

    drop table ##objectowners

    set nocount off

  • JC-3113

    SSCrazy Eights

    Points: 8366

    Hi RBarryYoung

    still having issues:

    E:\SQL_Server_SQL>sqlcmd

    1> --http://www.sqlservercentral.com/scripts/Administration/63631/

    2> --Find all objects in Server Owned by a Login

    3> --By RBarry Young, 2008/08/28

    4>

    5>

    6> CREATE Proc spLogin_OwnedObjects ( @Login as SYSNAME ) As

    7> /*

    8~ Display all objects in all DBs owned by the Login.

    9~

    10~ 2008-07-06 RBarryYoung Created.

    11~ 2008-08-28 RBarryYoung Added corrections for DBs with different Collations

    12~ (note that ReportingDBs have different Collations)

    13~

    14~ Test:

    15~ EXEC spLogin_OwnedObjects 'sa'

    16~ */

    17> declare @sql varchar(MAX), @DB_Objects varchar(MAX)

    18> Select @DB_Objects = ' L.name COLLATE DATABASE_DEFAULT as Login, U.Name COLLATE DATABASE_DEFAULT as [User]

    19~ , o.name COLLATE DATABASE_DEFAULT as [name]

    20~ , o.object_id

    21~ , o.principal_id

    22~ , o.schema_id

    23~ , o.parent_object_id

    24~ , o.type COLLATE DATABASE_DEFAULT as [type]

    25~ , o.type_desc COLLATE DATABASE_DEFAULT as [type_desc]

    26~ , o.create_date

    27~ , o.modify_date

    28~ , o.is_ms_shipped

    29~ , o.is_published

    30~ , o.is_schema_published

    31~ From %D%.sys.objects o

    32~ Join %D%.sys.database_principals u

    33~ ON Coalesce(o.principal_id

    34~ , (Select S.Principal_ID from %D%.sys.schemas S Where S.Schema_ID = O.schema_id))

    35~ = U.principal_id

    36~ Left Join %D%.sys.server_principals L on L.sid = u.sid

    37~ '

    38>

    39> Select @sql = 'SELECT * FROM

    40~ (Select '+Cast(database_id as varchar(9))+' as DBID, ''master'' as DBName, '

    41> + Replace(@DB_objects, '%D%', [name])

    42> From master.sys.databases

    43> Where [name] = 'master'

    44>

    45> Select @sql = @sql + 'UNION ALL Select '+Cast(database_id as varchar(9))+', '''+[name]+''', '

    46> + Replace(@DB_objects, '%D%', [name])

    47> From master.sys.databases

    48> Where [name] != 'master'

    49>

    50> Select @sql = @sql + ') oo Where Login = ''' + @Login + ''''

    51>

    52> print @sql

    53> EXEC (@sql)

    54> go

    1>

    2> EXEC spLogin_OwnedObjects 'sa'

    3> go

    SELECT * FROM

    (Select 1 as DBID, 'master' as DBName, L.name COLLATE DATABASE_DEFAULT as Login, U.Name COLLATE DATABASE_DEFAULT as [User]

    , o.name COLLATE DATABASE_DEFAULT as [name]

    , o.object_id

    , o.principal_id

    , o.schema_id

    , o.parent_object_id

    , o.type COLLATE DATABASE_DEFAULT as [type]

    , o.type_desc COLLATE DATABASE_DEFAULT as [type_desc]

    , o.create_date

    , o.modify_date

    , o.is_ms_shipped

    , o.is_published

    , o.is_schema_published

    From master.sys.objects o

    Join master.sys.database_principals u

    ON Coalesce(o.principal_id

    , (Select S.Principal_ID from master.sys.schemas S Where S.Schema_ID = O.schema_id))

    = U.principal_id

    Left Join master.sys.server_principals L on L.sid = u.sid

    UNION ALL Select 9, 'AdventureWorks', L.name COLLATE DATABASE_DEFAULT as Login, U.Name COLLATE DATABASE_DEFAULT as [User]

    , o.name COLLATE DATABASE_DEFAULT as [name]

    , o.object_id

    , o.principal_id

    , o.schema_id

    , o.parent_object_id

    , o.type COLLATE DATABASE_DEFAULT as [type]

    , o.type_desc COLLATE DATABASE_DEFAULT as [type_desc]

    , o.create_date

    , o.modify_date

    , o.is_ms_shipped

    , o.is_published

    , o.is_schema_published

    From AdventureWorks.sys.objects o

    Join AdventureWorks.sys.database_principals u

    ON Coalesce(o.principal_id

    , (Select S.Principal_ID from AdventureWorks.sys.schemas S Where S.Schema_ID = O.schema_id))

    = U.principal_id

    Left Join AdventureWorks.sys.server_principals L on L.sid = u.sid

    UNION ALL Select 8, 'AdventureWorksDW', L.name COLLATE DATABASE_DEFAULT as Login, U.Name COLLATE DATABASE_DEFAULT as [User]

    , o.name COLLATE DATABASE_DEFAULT as [name]

    , o.object_id

    , o.principal_id

    , o.schema_id

    , o.parent_object_id

    , o.type COLLATE DATABASE_DEFAULT as [type]

    , o.type_desc COLLATE DATABASE_DEFAULT as [type_desc]

    , o.create_date

    , o.modify_date

    , o.is_ms_shipped

    , o.is_published

    , o.is_schema_published

    From AdventureWorksDW.sys.objects o

    Join AdventureWorksDW.sys.database_principals u

    ON Coalesce(o.principal_id

    , (Select S.Principal_ID from AdventureWorksDW.sys.schemas S Where S.Schema_ID = O.schema_id))

    = U.principal_id

    Left Join AdventureWorksDW.sys.server_principals L on L.sid = u.sid

    UNION ALL Select 10, 'AdventureWorksLT', L.name COLLATE DATABASE_DEFAULT as Login, U.Name COLLATE DATABASE_DEFAULT as [User]

    , o.name COLLATE DATABASE_DEFAULT as [name]

    , o.object_id

    , o.principal_id

    , o.schema_id

    , o.parent_object_id

    , o.type COLLATE DATABASE_DEFAULT as [type]

    , o.type_desc COLLATE DATABASE_DEFAULT as [type_desc]

    , o.create_date

    , o.modify_date

    , o.is_ms_shipped

    , o.is_published

    , o.is_schema_published

    From AdventureWorksLT.sys.objects o

    Join AdventureWorksLT.sys.database_principals u

    ON Coalesce(o.principal_id

    , (Select S.Principal_ID from AdventureWorksLT.sys.schemas S Where S.Schema_ID = O.schema_id))

    = U.principal_id

    Left Join AdventureWorksLT.sys.server_principals L on L.sid = u.sid

    UNION ALL Select 3, 'model', L.name COLLATE DATABASE_DEFAULT as Login, U.Name COLLATE DATABASE_DEFAULT as [User]

    , o.name COLLATE DATABASE_DEFAULT as [name]

    , o.object_id

    , o.principal_id

    , o.schema_id

    , o.parent_object_id

    , o.type COLLATE DATABASE_DEFAULT as [type]

    , o.type_desc COLLATE DATABASE_DEFAULT as [type_desc]

    , o.create_date

    , o.modify_date

    , o.is_ms_shipped

    , o.is_published

    , o.is_schema_published

    From model.sys.objects o

    Join model.sys.database_principals u

    ON Coalesce(o.principal_id

    , (Select S.Principal_ID from model.sys.schemas S Where S.Schema_ID = O.schema_id))

    = U.principal_id

    Left Join model.sys.server_principals L on L.sid = u.sid

    UNION ALL Select 4, 'msdb', L.name COLLATE DATABASE_DEFAULT as Login, U.Name COLLATE DATABASE_DEFAULT as [User]

    , o.name COLLATE DATABASE_DEFAULT as [name]

    , o.object_id

    , o.principal_id

    , o.schema_id

    , o.parent_object_id

    , o.type COLLATE DATABASE_DEFAULT as [type]

    , o.type_desc COLLATE DATABASE_DEFAULT as [type_desc]

    , o.create_date

    , o.modify_date

    , o.is_ms_shipped

    , o.is_published

    , o.is_schema_published

    From msdb.sys.objects o

    Join msdb.sys.database_principals u

    ON Coalesce(o.principal_id

    , (Select S.Principal_ID from msdb.sys.schemas S Where S.Schema_ID = O.schema_id))

    = U.principal_id

    Left Join msdb.sys.server_principals L on L.sid = u.sid

    UNION ALL Select 14, 'NorthWind', L.name COLLATE DATABASE_DEFAULT as Login, U.Name COLLATE DATABASE_DEFAULT as [User]

    , o.name COLLATE DATABASE_DEFAULT as [name]

    , o.object_id

    , o.principal_id

    , o.schema_id

    , o.parent_object_id

    , o.type COLLATE DATABASE_DEFAULT as [type]

    , o.type_desc COLLATE DATABASE_DEFAULT as [type_desc]

    , o.create_date

    , o.modify_date

    , o.is_ms_shipped

    , o.is_published

    , o.is_schema_published

    From NorthWind.sys.objects o

    Join NorthWind.sys.database_principals u

    ON Coalesce(o.principal_id

    , (Select S.Principal_ID from NorthWind.sys.schemas S Where S.Schema_ID = O.schema_id))

    = U.principal_id

    Left Join NorthWind.sys.server_principals L on L.sid = u.sid

    UNION ALL Select 20, 'QA_SharedServices1_DB', L.name COLLATE DATABASE_DEFAULT as Login, U.Name COLLATE DATABASE_DEFAULT as [User]

    , o.name COLLATE DATABASE_DEFAULT as [name]

    , o.object_id

    , o.principal_id

    , o.schema_id

    , o.parent_object_id

    , o.type COLLATE DATABASE_DEFAULT as [type]

    , o.type_desc COLLATE DATABASE_DEFAULT as [type_desc]

    , o.create_date

    , o.modify_date

    , o.is_ms_shipped

    , o.is_published

    , o.is_schema_published

    From QA_SharedServices1_DB.sys.objects o

    Join QA_SharedServices1_DB.sys.database_principals u

    ON Coalesce(o.principal_id

    , (Select S.Principal_ID from QA_SharedServices1_DB.sys.schemas S Where S.Schema_ID = O.schema_id))

    = U.principal_id

    Left Join QA_SharedServices1_DB.sys.server_principals L on L.sid = u.sid

    UNION ALL Select 21, 'QA_SharedServices1_Search_DB', L.name COLLATE DATABASE_DEFAULT as Login, U.Name COLLATE DATABASE_DEFAULT as [User]

    , o.name COLLATE DATABASE_DEFAULT as [name]

    , o.object_id

    , o.principal_id

    , o.schema_id

    , o.parent_object_id

    , o.type COLLATE DATABASE_DEFAULT as [type]

    , o.type_desc COLLATE DATABASE_DEFAULT as [type_desc]

    , o.create_date

    , o.modify_date

    , o.is_ms_shipped

    , o.is_published

    , o.is_schema_published

    From QA_SharedServices1_Search_DB.sys.objects o

    Join QA_SharedServices1_Search_DB.sys.database_principals u

    ON Coalesce(o.principal_id

    , (Select S.Principal_ID from QA_SharedServices1_Search_DB.sys.schemas S Where S.Schema_ID = O.schema_id))

    = U.principal_id

    Left Join QA_SharedServices1_Search_DB.sys.server_principals L on L.sid = u.sid

    UNION ALL Select 7, 'QA_SharePoint_Config', L.name COLLATE DATABASE_DEFAULT as Login, U.Name COLLATE DATABASE_DEFAULT as [User]

    , o.name COLLATE DATABASE_DEFAULT as [name]

    , o.object_id

    , o.principal_id

    , o.schema_id

    , o.parent_object_id

    , o.type COLLATE DATABASE_DEFAULT as [type]

    , o.type_desc COLLATE DATABASE_DEFAULT as [type_desc]

    , o.create_date

    , o.modify_date

    , o.is_ms_shipped

    , o.is_published

    , o.is_schema_published

    From QA_SharePoint_Config.sys.objects o

    Join QA_SharePoint_Config.sys.database_principals u

    ON Coalesce(o.principal_id

    , (Select S.Principal_ID from QA_SharePoint_Config.sys.schemas S Where S.Schema_ID = O.schema_id))

    = U.principal_id

    Left Join QA_SharePoint_Config.sys.server_principals L on L.sid = u.sid

    UNION ALL Select 17, 'QA_WSS_Content_Home', L.name COLLATE DATABASE_DEFAULT as Login, U.Name COLLATE DATABASE_DEFAULT as [User]

    , o.name COLLATE DATABASE_DEFAULT as [name]

    , o.o

    Msg 102, Level 15, State 1, Server NSAB-SS80-SQL-N, Line 262

    Incorrect syntax near '-'.

    Msg 102, Level 15, State 1, Server NSAB-SS80-SQL-N, Line 265

    Incorrect syntax near '-'.

    Msg 102, Level 15, State 1, Server NSAB-SS80-SQL-N, Line 281

    Incorrect syntax near '-'.

    Msg 102, Level 15, State 1, Server NSAB-SS80-SQL-N, Line 284

    Incorrect syntax near '-'.

    Msg 102, Level 15, State 1, Server NSAB-SS80-SQL-N, Line 338

    Incorrect syntax near '-'.

    Msg 102, Level 15, State 1, Server NSAB-SS80-SQL-N, Line 341

    Incorrect syntax near '-'.

    Msg 102, Level 15, State 1, Server NSAB-SS80-SQL-N, Line 357

    Incorrect syntax near '-'.

    Msg 102, Level 15, State 1, Server NSAB-SS80-SQL-N, Line 360

    Incorrect syntax near '-'.

    Msg 102, Level 15, State 1, Server NSAB-SS80-SQL-N, Line 376

    Incorrect syntax near '-'.

    Msg 102, Level 15, State 1, Server NSAB-SS80-SQL-N, Line 379

    Incorrect syntax near '-'.

    Msg 102, Level 15, State 1, Server NSAB-SS80-SQL-N, Line 724

    Incorrect syntax near ')'.

    1>

  • RBarryYoung

    SSC Guru

    Points: 143327

    Hmmm, weird. It's obvious what's wrong, but I though that I had fixed it long ago. Well, this should fix it anyway:

    ALTER Proc spLogin_OwnedObjects ( @Login as SYSNAME ) As

    /*

    Display all objects in all DBs owned by the Login.

    2008-07-06 RBarryYoung Created.

    Test:

    EXEC spLogin_OwnedObjects 'sa'

    */

    declare @sql varchar(MAX), @DB_Objects varchar(MAX)

    Select @DB_Objects = ' L.name COLLATE DATABASE_DEFAULT as Login, U.Name COLLATE DATABASE_DEFAULT as [User]

    , o.name COLLATE DATABASE_DEFAULT as [name]

    , o.object_id

    , o.principal_id

    , o.schema_id

    , o.parent_object_id

    , o.type COLLATE DATABASE_DEFAULT as [type]

    , o.type_desc COLLATE DATABASE_DEFAULT as [type_desc]

    , o.create_date

    , o.modify_date

    , o.is_ms_shipped

    , o.is_published

    , o.is_schema_published

    From [%D%].sys.objects o

    Join [%D%].sys.database_principals u

    ON Coalesce(o.principal_id

    , (Select S.Principal_ID from [%D%].sys.schemas S Where S.Schema_ID = O.schema_id))

    = U.principal_id

    Left Join [%D%].sys.server_principals L on L.sid = u.sid

    '

    Select @sql = 'SELECT * FROM

    (Select '+Cast(database_id as varchar(9))+' as DBID, ''master'' as DBName, '

    + Replace(@DB_objects, '%D%', [name])

    From master.sys.databases

    Where [name] = 'master'

    Select @sql = @sql + 'UNION ALL Select '+Cast(database_id as varchar(9))+', '''+[name]+''', '

    + Replace(@DB_objects, '%D%', [name])

    From master.sys.databases

    Where [name] != 'master'

    Select @sql = @sql + ') oo Where Login = ''' + @Login + ''''

    print @sql

    EXEC (@sql)

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.
    [/font]
    [font="Verdana"] "Performance is our middle name."[/font]

  • JC-3113

    SSCrazy Eights

    Points: 8366

    Excellent RBarryYoung

    it was not obvious to me apparently.. what was it ?

    Thanks

    Jim

  • RBarryYoung

    SSC Guru

    Points: 143327

    Unquoted names (i.e., no "[..]") cannot have certain characters in them like spaces or "-". So I just added the brackets around the database names ("%D%" --> "[%D%]").

    Thing is, I thought that I had done that a long time ago...

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.
    [/font]
    [font="Verdana"] "Performance is our middle name."[/font]

  • JC-3113

    SSCrazy Eights

    Points: 8366

    Well at least it was obvious to you and you fixed it quickly

    thanks again

    jim

Viewing 15 posts - 1 through 15 (of 29 total)

You must be logged in to reply to this topic. Login to reply