Re: SQL2000 UserPermissions for Database

what parameters /changes are to be added for selecting only "user
created" database in the first section(a)

If you want to ignore database access via 'guest' and sysadmin role membership, I believe you can change this WHERE clause:

WHERE
sid = SUSER_SID(@Login) and
--OR
-- IS_SRVROLEMEMBER(''sysadmin'', @Login) = 1 OR
-- (name<> N''guest'' AND hasdbaccess = 1)
-----------------------
(name<> N''guest'' AND hasdbaccess = 1)
-----------------------

to

WHERE sid = SUSER_SID(@Login)


One thing I didn't mention earlier is that this technique won't detect database access via Windows group membership. However, it should work fine for Windows and SQL logins.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Arejan" <areejan2000@xxxxxxxxx> wrote in message news:1166110610.395380.98370@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
EXEC usp_ListDatabasesForLogin 'userx'

is returning

master
msdb
Northwind
pubs
tempdb
databaseA
databaseF

have to exclude (found these having guest permit)
master
msdb
Northwind
pubs
tempdb


and only show

databaseA
databaseF



i made changes in sp
a) SET @SqlStatement =
N'USE ' + @DatabaseName +
N' IF EXISTS(
SELECT 1
FROM dbo.sysusers
WHERE
sid = SUSER_SID(@Login) and
--OR
-- IS_SRVROLEMEMBER(''sysadmin'', @Login) = 1 OR
-- (name<> N''guest'' AND hasdbaccess = 1)
-----------------------
(name<> N''guest'' AND hasdbaccess = 1)
-----------------------
)
BEGIN
INSERT INTO #DatabaseList
VALUES(@DatabaseName)
END'

and

b)

SELECT DatabaseName
FROM #DatabaseList
---------------------------
where DatabaseName<>'master'
-------------------------
ORDER BY DatabaseName
DROP TABLE #DatabaseList
GO


if the above sp changes are correct

what parameters /changes are to be added for selecting only "user
created" database in the first section(a)


instead of using (b)
---------------------------
where DatabaseName<>'master'
-------------------------


.