October 4, 2002 at 1:46 pm
When I execute The Stored Procedure SP_Databases in ADO, I get the following error if the user does not have any permissions in a Database on the server.
Error: Server user 'test' is not a valid user in database 'LimitedAccess'.
The Server has 6 databases, and the 'LimitedAccess' database is the only one that the user does not have permissions for.
Can someone help me with a way to get a list of the available databases for a user without using sp_databases and getting an error?
Wes Grant
http://www.aspenterprisemanager.com/
Edited by - wgrant0001 on 10/04/2002 1:48:16 PM
Edited by - wgrant0001 on 10/04/2002 1:48:46 PM
Wes Grant
http://www.aspenterprisemanager.com/
October 4, 2002 at 2:26 pm
select name from sysdatabases.
Or
Grant guest account to the 'LimitedAccess' database but don't grant any permissions to it to access any user's objtecs.
October 4, 2002 at 6:54 pm
The information is stored in syslogins or sysusers (sorry no SQL Server here to double check). You can query the [name] column for the user ot the [id] column if you know their id as defined in master sysxlogins or syslogins.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
October 4, 2002 at 6:58 pm
I actually have found an answer. The sp_helpdb command, without a database name specified, returns information about all of the databases that users have permission to see.
Wes Grant
http://www.aspenterprisemanager.com/
Wes Grant
http://www.aspenterprisemanager.com/
October 7, 2002 at 4:31 am
You can try
SELECT name FROM master.dbo.sysdatabases WHERE has_dbaccess(name) = 1 ORDER BY name;
Regards
el.c. (http://www.mylittletools.net)
myLittleTools.net :: web-based applications
myLittleTools.net :: web-based applications
http://www.mylittletools.net
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply