November 23, 2005 at 9:48 am
I am trying to clean up unneeded logins and I get errors when trying to delete some:
You cannot delete the selected login ID because that login ID owns objects in one or more databases.
Is there a way to get a server wide listing of any objects owned by a particular login? I can't seem to find the objects referenced.
...
-- FORTRAN manual for Xerox Computers --
November 24, 2005 at 4:49 am
Try the sp_helprotect command against the master database.
Have a look at the command on BOL. I think there are switches that you can use to specify a given logins permissions.
regards
Carl
November 25, 2005 at 7:05 am
DECLARE curDBs CURSOR
READ_ONLY
FOR select name from sysdatabases
DECLARE @name varchar(40)
DECLARE @cmdstr varchar(8000)
SELECT @cmdstr = ''
OPEN curDBs
FETCH NEXT FROM curDBs INTO @name
WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status <> -2)
BEGIN
EXEC master..xp_sprintf @cmdstr OUTPUT, 'select sysobjects.name, sysobjects.type, user_name(%s..sysusers.uid) from %s..sysusers inner join sysobjects on (%s..sysusers.uid = sysobjects.uid)',@name,@name,@name
EXEC (@cmdstr)
END
FETCH NEXT FROM curDBs INTO @name
END
CLOSE curDBs
DEALLOCATE curDBs
GO
regards,
Jan
November 28, 2005 at 10:20 am
I think showing all objects and their owners is overkill.
This will find all objects in all databases owned by a given login:
set
nocount on
create table #owned (objectname varchar(500))
exec sp_msforeachdb 'insert into #owned
select ''[?].['' + RTRIM(su.name) + ''].['' + RTRIM(so.name) + '']'' as objectname
from [?].dbo.sysobjects so inner join [?].dbo.sysusers su on su.uid = so.uid
where su.sid = (select sid from master.dbo.syslogins where name = ''<login>'')'
select * from #owned
drop table #owned
This will not check system databases, but that should not be an issue.
November 28, 2005 at 11:43 am
Thanx, OldHand. that script did the job.
Jay
...
-- FORTRAN manual for Xerox Computers --
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply