Re: SQL2000 UserPermissions for Database

Since your last message mentioned loading a combobox, I assumed you wanted a list of databases the current user could access rather than an admin application, presumably run by privileged users. I believe the proc below will meet your requirement and you can execute the code directly if needed.


CREATE PROC dbo.usp_ListDatabasesForLogin
@Login sysname = NULL
AS
SET NOCOUNT ON
DECLARE
@DatabaseName sysname,
@SqlStatement nvarchar(4000)

--use current login if @Login not specified
IF @Login IS NULL SET @Login = SUSER_SNAME()

IF OBJECT_ID(N'tempdb..#DatabaseList') IS NOT NULL
DROP TABLE #DatabaseList
CREATE TABLE #DatabaseList
(
DatabaseName sysname
)

DECLARE DatabaseList
CURSOR LOCAL FAST_FORWARD READ_ONLY FOR
SELECT name
FROM master..sysdatabases
WHERE HAS_DBACCESS(name) = 1

OPEN DatabaseList
WHILE 1 = 1
BEGIN
FETCH NEXT FROM DatabaseList INTO @DatabaseName
IF @@FETCH_STATUS = -1 BREAK

SET @SqlStatement =
N'USE ' + @DatabaseName +
N' IF EXISTS(
SELECT 1
FROM dbo.sysusers
WHERE
sid = SUSER_SID(@Login) OR
IS_SRVROLEMEMBER(''sysadmin'', @Login) = 1 OR
(name = N''guest'' AND hasdbaccess = 1)
)
BEGIN
INSERT INTO #DatabaseList
VALUES(@DatabaseName)
END'

EXEC sp_executesql
@SqlStatement,
N'@Login sysname, @DatabaseName sysname',
@Login = @Login,
@DatabaseName = @DatabaseName
END
CLOSE DatabaseList
DEALLOCATE DatabaseList

SELECT DatabaseName
FROM #DatabaseList
ORDER BY DatabaseName

DROP TABLE #DatabaseList
GO


--
Hope this helps.

Dan Guzman
SQL Server MVP

"Arejan" <areejan2000@xxxxxxxxx> wrote in message news:1165483192.541450.79840@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
Dan

SELECT name
FROM master.dbo.sysdatabases
WHERE HAS_DBACCESS(name) = 1
ORDER BY name

gives a list of all databases

databaseA
databaseB
databaseC
databaseD
databaseE
databaseF
master
model
pubs
...

( log in as sa (administrator))

i want to show only 2 databases of which Userx has previlage to login


Database user
databaseA Userx
databaseF Userx

as other databses are not used in this context

is there an select query for this?


.