http://www.sqlservercentral.com/blogs/brian_kelley/2009/02/22/mapping-database-principals-to-server-principals/

Printed 2014/12/17 10:56PM

Mapping Database Principals to Server Principals

2009/02/22

A question on the forum asked how to find all the database mappings for a particular login. If you're on SQL Server 2000 or below, the tables you want to use are syslogins in the master database and sysusers in each database. The key to tying the login to a user is the SID. So if you know the login's SID, you set up your WHERE clause against sysusers and if you get a match, that login has a user mapping in that database. With SQL Server 2005 and above, you'll need to use sys.server_principals and sys.database_principals, respectively. The code I'll include shortly is the SQL Server 2005 and above example. 

Now this query fails to report access in three specific situations. That's because in these cases the login has implicit rights into the database. Those cases are:

Here's the query. If you've got a lot of databases, it may take a little bit to run.

DECLARE @LoginName sysname;
DECLARE @DatabaseName sysname;
DECLARE @SQL NVARCHAR(4000);
DECLARE @sid VARCHAR(255);

SET @LoginName '<Login Name To Search On>';
SET @sid (SELECT sid FROM sys.server_principals WHERE [name] @LoginName);

CREATE TABLE #Mappings (
  
DatabaseName sysname,
  
UserName sysname);

DECLARE cursDatabases CURSOR FAST_FORWARD FOR SELECT name FROM sys.databases;

OPEN cursDatabases;

FETCH NEXT FROM cursDatabases INTO @DatabaseName;

WHILE (@@FETCH_STATUS 0)
BEGIN
   SET 
@SQL 'INSERT INTO #Mappings (DatabaseName, UserName)
             SELECT ''' 
@DatabaseName ''', name
             FROM [' 
@DatabaseName '].sys.database_principals
             WHERE sid = ''' 
@sid ''';';
                
   
EXEC(@SQL);
   
   
FETCH NEXT FROM cursDatabases INTO @DatabaseName;
END

CLOSE 
cursDatabases;
DEALLOCATE cursDatabases;

SELECT FROM #Mappings;

DROP TABLE #Mappings;

EDIT: The last case I mentioned I was incorrect. If you check sys.database_principals, you'll see that the owner of the database does show as dbo. The dbo user is mapped to the login that owns the database. Now you can go database by database to find out who has access in such a manner or you can query sys.databases to determine the owner.

 


Copyright © 2002-2014 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.