July 7, 2008 at 2:47 pm
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.
July 7, 2008 at 4:30 pm
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]
July 7, 2008 at 8:38 pm
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]
July 7, 2008 at 8:41 pm
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]
July 8, 2008 at 4:24 am
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
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
July 8, 2008 at 8:50 am
Thank you RBarryYoung. But still have errors.
Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'User'.
....
July 8, 2008 at 8:51 am
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
July 8, 2008 at 3:23 pm
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]
July 8, 2008 at 3:26 pm
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]
July 8, 2008 at 9:52 pm
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
July 8, 2008 at 9:57 pm
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.
July 10, 2008 at 2:55 pm
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 ?
July 10, 2008 at 3:34 pm
...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
July 10, 2008 at 3:54 pm
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]
July 10, 2008 at 5:11 pm
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 39 total)
You must be logged in to reply to this topic. Login to reply