CODEWITHSUNDEEP
sql-serversql-server-2005
Michael Capobianco
Michael Capobianco

Reputation: 812

Query all Databases a User has Access to as Administrator

I'm looking to query all databases mapped to a user, similar to Security > Logins > Properties > User Mapping.

This may be done in SQL 2005 if possible

For example, something similar to:

SELECT name
FROM sys.databases
WHERE HAS_DBACCESS(name) = 1

But perform the query from an administrative user, as opposed to running the above query as the user itself.

How would something like this be performed?

Thank you.

Upvotes: 0

Views: 7702

Answers (2)

anon
anon

Reputation:

Well this might be a start, probably not the nice output you'd hope for (and it produces two resultsets):

EXEC sp_helplogins N'floob';

But it does work on SQL Server 2000. If you want to try and replicate some of the functionality in the procedure, you can see how it's checking for permissions, basically a cursor through every database. On SQL Server 2000:

EXEC sp_helptext N'sp_helplogins';

On 2005+ I much prefer the output of OBJECT_DEFINITION():

SELECT OBJECT_DEFINITION(OBJECT_ID(N'sys.sp_helplogins'));

So you could write your own cursor based on similar logic, and make the output prettier...

Here is a quick (and not complete) example, doesn't cover much but an idea to get started if the above is not sufficient:

DECLARE @login NVARCHAR(255);
SET @login = N'foobarblat';

-- above would be an input parameter to a procedure, I presume

CREATE TABLE #dbs(name SYSNAME);

DECLARE @sql NVARCHAR(MAX);
SET @sql = N'';

SELECT @sql = @sql + N'INSERT #dbs SELECT ''' + name + ''' FROM ' 
  + QUOTENAME(name) + '.sys.database_principals AS u
  INNER JOIN sys.server_principals AS l
  ON u.sid = l.sid
  WHERE l.name = @login;'
FROM sys.databases 
WHERE state_desc = 'ONLINE'
AND user_access_desc = 'MULTI_USER';

EXEC sp_executesql @sql, N'@login SYSNAME', @login;

SELECT name FROM #dbs;

DROP TABLE #dbs;

As I said, this is not complete. Won't know if the user has been denied connect, is member of deny reader/writer roles, won't show the alias if the user name in the db doesn't match the login, etc. You can dig into more details from sp_helplogins depending on what you want to show.

Upvotes: 2

Russell Fox
Russell Fox

Reputation: 5435

The EXECUTE AS functionality was added in the 2005 release, so I don't think you can run that in 2000. You could probably mimick it by putting the relevant code in a job and setting the job owner to an admin user, but it would process with the job not inline.

Upvotes: 0

Related Questions