July 11, 2008 at 12:54 pm
I tried throwing all of my query into a proc and having issues. It executes and creates correctly. But, when I go back and try to run it, it sits there and 'executes...' forever 🙁
CREATE PROC dbo.OrphanUsers1
AS
BEGIN
CREATE TABLE #Results
(
[Server] sysname COLLATE Latin1_General_CI_AS,
[Database] sysname COLLATE Latin1_General_CI_AS,
[Orphaned User] sysname COLLATE Latin1_General_CI_AS,
[NmbOfObjects] 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 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 serverproperty(''servername'') as [Server], ''' + @dbName + ''' as [Database],CAST(su.name AS sysname) COLLATE Latin1_General_CI_AS AS [Orphaned User],
NbrOfObjects FROM ' + @dbName + '..sysusers su JOIN
(select count(*) as [NmbOfObjects], b.name
from ' + @dbName + '..sysobjects a,
' + @dbName + '..sysusers b
where a.uid = b.uid
group by b.name) uo ON
su.name = uo.name
WHERE su.islogin = 1
AND su.name <> ''guest''
AND NOT EXISTS
(
SELECT 1
FROM master..syslogins sl
WHERE su.sid = sl.sid)'
INSERT INTO #Results EXEC (@Qry)
END
SELECT *
FROM #Results
ORDER BY [Database], [Orphaned User]
END
edit::::::::::
in addition, when i kill the script, i receive
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, = or when the subquery is used as an expression.
July 11, 2008 at 1:18 pm
SET @DBName = ''
WHILE @DBName IS NOT NULL
BEGIN
the logic above could possibly need re visiting
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
July 11, 2008 at 1:23 pm
And run Profiler to see if things are moving while it's executing.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
July 11, 2008 at 1:49 pm
There are some definite logic problems.:w00t:
I think this part is not making sense to me:
AND NOT EXISTS
(
SELECT 1
FROM master..syslogins sl
WHERE su.sid = sl.sid)'
I worked with what you posted and put the following together:
CREATE PROC dbo.OrphanUsers1
AS
BEGIN
CREATE TABLE #Results
(
[Server] nvarchar(200) ,
[Database] nvarchar(200),
[Orphaned User] nvarchar(200) ,
[NmbOfObjects] int
)
SET NOCOUNT ON
declare cursor_OrphanUsers1 cursor for
SELECT [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
DECLARE @DBName nvarchar(200), @Qry nvarchar(4000)
open cursor_OrphanUsers1
FETCH NEXT FROM cursor_OrphanUsers1 INTO @DBName
WHILE (@@FETCH_STATUS = 0)
BEGIN
SET @Qry = 'SELECT convert(nvarchar(200),serverproperty(''servername'')) as [Server], ''' + convert(nvarchar(200),@dbName) + ''' as [Database],convert(nvarchar(200),su.name) AS [Orphaned User],
UO.NmbOfObjects FROM ' + @dbName + '..sysusers su JOIN
(select count(*) as [NmbOfObjects], b.name
from ' + @dbName + '..sysobjects a,
' + @dbName + '..sysusers b
where a.uid = b.uid
group by b.name) uo ON
su.name = uo.name
WHERE su.islogin = 1
AND su.name <> ''guest''
AND NOT EXISTS
(
SELECT 1
FROM master..syslogins sl
WHERE su.sid = sl.sid)'
--Print @Qry
INSERT INTO #Results EXEC (@Qry)
FETCH NEXT FROM cursor_OrphanUsers1 INTO @DBName
END
SELECT *
FROM #Results
ORDER BY [Database], [Orphaned User]
END
close cursor_OrphanUsers1
deallocate cursor_OrphanUsers1
July 11, 2008 at 3:12 pm
i could be wrong but i get the feeling he was trying to achieve it without the use of a cursor
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
July 11, 2008 at 3:25 pm
I guess you could assume that. I didn't feel this stored procedure would choke on a cursor. I didn't make it a priority when looking at the problem. Next lunch break I will try another path...
July 11, 2008 at 3:49 pm
starflyer (7/11/2008)
I didn't feel this stored procedure would choke on a cursor.
couldnt agree with you more
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply