Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

K. Brian Kelley - Databases, Infrastructure, and Security

IT Security, MySQL, Perl, SQL Server, and Windows technologies.

Mapping Database Principals to Server Principals

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:

  • The login is a member of the sysadmin fixed server role. In this case the login comes in as dbo on all databases and you won't see a mapping in sysusers/sys.database_principals.
  • The login has CONTROL SERVER rights (SQL Server 2005 and above). In this case, the login functions as above on all databases. 
  • The login owns the database. In this case the login comes in as dbo for that particular database and you won't see a mapping in sysusers/sys.database_principals. * See EDIT: below.

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.

 

Comments

Posted by Steve Jones on 23 February 2009

Any way to query those three situations?

Posted by Anonymous on 23 February 2009

Yesterday I blogged about how to figure out what database principals corresponded to what server principals

Posted by Anonymous on 24 February 2009

This question comes up a lot in the forums: &quot;How do I know if the login owns any objects?&quot;

Posted by yzhu117 on 5 March 2009

It is a useful script to find mappings of login to users. Thanks, Brian.

I modified the script so that the script will find all login and user mappings in a server using another cursor. One problem is it will generate some error messages for some logins. It seems sql server cannot recognize the sids for those logins in the @SQL string. You could see the error if you remove the comment within catch block. Maybe it is just specific to my server.

DECLARE @LoginName sysname;

DECLARE @DatabaseName sysname;

DECLARE @SQL NVARCHAR(4000);

DECLARE @sid VARCHAR(255);

CREATE TABLE #Mappings (

 DatabaseName sysname,

 UserName sysname);

--select * from agmap.sys.database_principals

declare cursPrincipal cursor fast_forward for select name from sys.server_principals;

open cursPrincipal;

fetch next from cursPrincipal into @LoginName;

--SET @LoginName = '<Login Name To Search On>';

while(@@fetch_status=0)

begin

SET @sid = (SELECT sid FROM sys.server_principals WHERE [name] = @LoginName);

DECLARE cursDatabases CURSOR FAST_FORWARD FOR

SELECT name FROM sys.databases

where [name] not in ('master', 'model', 'msdb','tempdb','reportServer', 'reportServerTempDB' );

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 + ''' and sid is not null;' --whatever reason sid could be NULL

begin try

EXEC(@SQL);

end try

begin catch

--select ERROR_LINE() AS ErrorLine, ERROR_MESSAGE() as errorMsg,  @SQL  as SQLstr

end catch

  FETCH NEXT FROM cursDatabases INTO @DatabaseName;

END

CLOSE cursDatabases;

DEALLOCATE cursDatabases;

fetch next from cursPrincipal into @LoginName;

end

close cursPrincipal;

deallocate cursPrincipal;

SELECT * FROM #Mappings order by databasename;

DROP TABLE #Mappings;

Posted by K. Brian Kelley on 5 March 2009

Try this:

DECLARE @DatabaseName SYSNAME ;

DECLARE @SQL NVARCHAR(4000) ;

DECLARE @sid VARCHAR(255) ;

DECLARE @LoginName SYSNAME;

CREATE TABLE #Mappings

   (

     DatabaseName SYSNAME,

     LoginName SYSNAME,

     UserName SYSNAME

   ) ;

DECLARE cursPrincipal CURSOR fast_forward

   FOR SELECT  [name], [sid]

       FROM    sys.server_principals

       WHERE [type] IN ('S', 'U', 'G');

OPEN cursPrincipal ;

FETCH NEXT FROM cursPrincipal INTO @LoginName, @sid ;

WHILE( @@fetch_status = 0 )

   BEGIN

       DECLARE cursDatabases CURSOR FAST_FORWARD

           FOR SELECT  name

               FROM    sys.databases

               WHERE   [name] NOT IN ( 'master', 'model', 'msdb', 'tempdb',

                                       'reportServer', 'reportServerTempDB' ) ;

       OPEN cursDatabases ;

       FETCH NEXT FROM cursDatabases INTO @DatabaseName ;

       WHILE ( @@FETCH_STATUS = 0 )

           BEGIN

               SET @SQL = 'INSERT INTO #Mappings (DatabaseName, LoginName, UserName)

                           SELECT ''' + @DatabaseName + ''', ''' + @LoginName + ''', name

                           FROM [' + @DatabaseName + '].sys.database_principals

                           WHERE sid = ''' + @sid + ''';'

               EXEC ( @SQL ) ;

               FETCH NEXT FROM cursDatabases INTO @DatabaseName ;

           END

       CLOSE cursDatabases ;

       DEALLOCATE cursDatabases ;

       FETCH NEXT FROM cursPrincipal INTO @LoginName, @sid ;

   END

CLOSE cursPrincipal ;

DEALLOCATE cursPrincipal ;

SELECT  *

FROM    #Mappings

ORDER BY databasename ;

DROP TABLE #Mappings ;

Posted by yzhu117 on 5 March 2009

HI, Brian:

The problem is the same. I copied an example of value in @SQL variable that caused the error:

INSERT INTO #Mappings (DatabaseName, LoginName, UserName)

SELECT 'DBName', 'DataUser', name FROM [DBName].sys.database_principals WHERE sid = '6Rm¬æ'K«Ó¼q7';

Msg 102, Level 15, State 1, Line 7

Incorrect syntax near 'K'.

Msg 105, Level 15, State 1, Line 7

Unclosed quotation mark after the character string ';'.

Please notice that there is a single quote before K within the sid.

Also I did not see much differences in the logic in your new code. You limited the principals (but it may not be the cause of problem because I tried to limit the pricipal type only to S, the problem still exists) and select sid and login in one search trip. In my version, the sid was assigned in a different search trip, though it may cost a little more computation. Thanks.

Leave a Comment

Please register or log in to leave a comment.