Script to find all objects owned by a user (loginname)

  • Vivien Xing

    SSChampion

    Points: 12297

    Need the script to find out all the objects owned by a login. Should apply to all the databases on a server (SQL2000 and SQL2005).

    Anyone has any idea? Thanks in advance.

  • RBarryYoung

    SSC Guru

    Points: 143327

    Harder than I thought, but try this:

    alter Proc spLogin_OwnedObjects ( @Login as SYSNAME ) As

    --spLogin_OwnedObjects 'sa'

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

    Select @DB_Objects = ' L.name as Login, U.*

    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]

  • RBarryYoung

    SSC Guru

    Points: 143327

    Oops, found a bug.

    Corrected version:

    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 as Login, U.Name 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]

  • RBarryYoung

    SSC Guru

    Points: 143327

    Sorry, I just noticed that you wanted a SQL2000 version as well. This is only SQL2005 and the SQL2000 version would be significantly different. Possibly simpler though.

    [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]

  • Perry Whittle

    SSC Guru

    Points: 233678

    for SQL2000 try this, it returns any objects not owned by sys or dbo just change it to look for your login (instead of excluding). To catch all DB's wrap it in a SP maybe?

    select name

    from sysobjects

    where user_name(Uid) <> 'dbo' and user_name(Uid) <> 'sys'

    order by name

    -----------------------------------------------------------------------------------------------------------

    [font="Tahoma"]"Ya can't make an omelette without breaking just a few eggs"[/font] 😉

  • Vivien Xing

    SSChampion

    Points: 12297

    Thank you RBarryYoung. But still have errors.

    Msg 156, Level 15, State 1, Line 2

    Incorrect syntax near the keyword 'User'.

    ....

  • Vivien Xing

    SSChampion

    Points: 12297

    Thank you Perry.

    I modified a little.

    select user_name(uid) as user_name, name

    from sysobjects

    where user_name(uid) <> 'dbo' and user_name(uid) <> 'sys'

    order by user_name, name

  • RBarryYoung

    SSC Guru

    Points: 143327

    re-corrected version (sorry):

    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 as Login, U.Name 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]

  • RBarryYoung

    SSC Guru

    Points: 143327

    Vivien Xing (7/8/2008)


    Thank you Perry.

    I modified a little.

    select user_name(uid) as user_name, name

    from sysobjects

    where user_name(uid) <> 'dbo' and user_name(uid) <> 'sys'

    order by user_name, name

    Actually, it is not valid to exclude 'dbo' here. If a Login owns a database, then they are user 'dbo' in the database and they own all of the dbo-owned objects.

    [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]

  • Vivien Xing

    SSChampion

    Points: 12297

    Worked out a version for all databases (SQL2000).

    -- List all objects owned by any user(s) other than DBO.

    DECLARE @sql VARCHAR(500)

    SELECT @sql =

    'use [?]

    select db_name() as database_name

    select user_name(uid) as user_name, name, xtype

    from [?].dbo.sysobjects

    where user_name(uid) <> ''dbo'' and user_name(uid) <> ''sys''

    order by user_name, name'

    EXEC sp_MSforeachdb @sql

  • Vivien Xing

    SSChampion

    Points: 12297

    rbarryyoung (7/8/2008)


    Actually, it is not valid to exclude 'dbo' here. If a Login owns a database, then they are user 'dbo' in the database and they own all of the dbo-owned objects.

    I am trying to find out all the objects owned by a login before I drop it. You are right, this user/login may own a database. If this is the case, need sp_helpdb to find the db owner and transfer db ownership first.

  • Vivien Xing

    SSChampion

    Points: 12297

    rbarryyoung (7/8/2008)


    re-corrected version (sorry):

    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 as Login, U.Name 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)

    still errors ?

  • Topher

    SSCommitted

    Points: 1703

    ...would this suffice for both...

    select a.name, b.name from sysobjects a, sysusers b

    where a.uid = b.uid

    Then you could simply wrap it up in a proc in comparison w/ the one I have for ophaned logins...

    CREATE PROC dbo.ShowOrphanUsers

    AS

    BEGIN

    CREATE TABLE #Results

    (

    [Database Name] sysname COLLATE Latin1_General_CI_AS,

    [Orphaned User] sysname COLLATE Latin1_General_CI_AS

    )

    SET NOCOUNT ON

    DECLARE @DBName sysname, @Qry nvarchar(4000)

    SET @Qry = ''

    SET @DBName = ''

    WHILE @DBName IS NOT NULL

    BEGIN

    SET @DBName =

    (

    SELECT MIN(name)

    FROM master..sysdatabases

    WHERE name NOT IN

    (

    'master', 'model', 'tempdb', 'msdb',

    'distribution', 'pubs', 'northwind'

    )

    AND DATABASEPROPERTY(name, 'IsOffline') = 0

    AND DATABASEPROPERTY(name, 'IsSuspect') = 0

    AND name > @DBName

    )

    IF @DBName IS NULL BREAK

    SET @Qry = ' SELECT ''' + @DBName + ''' AS [Database Name],

    CAST(name AS sysname) COLLATE Latin1_General_CI_AS AS [Orphaned User]

    FROM ' + QUOTENAME(@DBName) + '..sysusers su

    WHERE su.islogin = 1

    AND su.name <> ''guest''

    AND NOT EXISTS

    (

    SELECT 1

    FROM master..sysxlogins sl

    WHERE su.sid = sl.sid

    )'

    INSERT INTO #Results EXEC (@Qry)

    END

    SELECT *

    FROM #Results

    ORDER BY [Database Name], [Orphaned User]

    END

  • RBarryYoung

    SSC Guru

    Points: 143327

    Vivien Xing (7/10/2008)


    rbarryyoung (7/8/2008)


    re-corrected version (sorry):

    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 as Login, U.Name 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)

    still errors ?

    You would have to tell me what the errors were since it works fine for me. I have tried it on three different servers now, and it still works.

    [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]

  • Vivien Xing

    SSChampion

    Points: 12297

    rbarryyoung (7/10/2008)


    Vivien Xing (7/10/2008)


    rbarryyoung (7/8/2008)


    re-corrected version (sorry):

    still errors ?

    You would have to tell me what the errors were since it works fine for me. I have tried it on three different servers now, and it still works.

    It was my mistake. I pointed to SQL2000.

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

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