I need to pull out how many databases are accessible for a particular user in SQL Server 2000/2005 using a query. Any help is appreciated. Thank You
asked
SQL Server - database access information
When you run this on a particular database in 2005, it will return you the users with permission to connect, listing as the database name, the login name and the database user name.
SELECT DB_NAME() AS DatabaseName, [sp].[name] AS LoginName, [dp].[name] AS DatabaseUserName
FROM [sys].[server_principals] AS [sp]
INNER JOIN
[sys].[database_principals] AS [dp]
ON [dp].[sid] = [sp].[sid]
INNER JOIN
[sys].[database_permissions] AS [dp1]
ON [dp1].[grantee_principal_id] = [dp].[principal_id]
WHERE [dp1].[type] = 'CO';
This is the 2000 equivalent:
SELECT DB_NAME() AS DatabaseName, SUSER_SNAME([s].[sid]) AS LoginName, [s].[name] AS DatabaseUserName
FROM [sys].[sysusers] [s]
WHERE [hasdbaccess] = 1 AND SUSER_SNAME([s].[sid]) IS NOT NULL
You can then execute this on the multiple databases however suits you best - either using SSMS 2008's multiple execution or some third party tool.
Having looked at Kev's answer, here's a combination of the two:
create TABLE #dbnames (dbname [sysname] NULL, LoginName [sysname] NULL, DatabaseUserName [sysname] NULL)
insert into #dbnames
EXEC sp_MSforeachdb 'SELECT ''?'' AS DatabaseName, SUSER_SNAME([s].[sid]) AS LoginName, [s].[name] AS DatabaseUserName
FROM [?]..[sysusers] [s]
WHERE [hasdbaccess] = 1 AND SUSER_SNAME([s].[sid]) IS NOT NULL'
select * from #dbnames
drop table #dbnames
Here's a solution that works without having to run against multiple databases. It also works on both 2000 and 2005, just because the system table is included in 2005 for backwards compatability!
it also uses the undocumented (and therefore unsupported) stored proc sp_MSforeachdb
create table #dbnames (dbname varchar(100))
insert into #dbnames
exec sp_MSforeachdb 'select ''?'' from sysusers where name = ''MyUser'' and hasdbaccess = 1'
select dbname from #dbnames
drop table #dbnames
