Home / server / Questions / 16872
In Process
Fatherjack
Asked: 2009-06-02 06:45:23 +0800 CST

xp_logininfo returns collation error

  • 772

I have a production SQL Server that is set with system databases as Latin1_General_CI_AS but not all user databases are the same, some have been created with SQL_Latin1_General_CP1_CI_AS, and several others - they have been created as part of 3rd party application installs etc.

Running Exec xp_logininfo in these databases causes a typical COLLATION error.

Is there any way around this? (copy and recode xp_logininfo versions for alternate collations? Change all dbs to be same collation and suffer fallout of applications crashing ..?)

all thoughts appreciated.

Edit #1 I am trying to review and eventually move towards managing security and users/logins. Currently if I run :

use DB1  
GO  
exec xp_logininfo  
go  
exec xp_logininfo 'domain\groupname'  
go  
exec xp_logininfo 'domain\username'  
go

I get 3 data sets, 1 a list of all users, 2 details of the groups access and 3 how (by virtue of group membership) the specified login gets to access data in DB1

However, if I execute: 

use DB2  
GO  
exec xp_logininfo   
go  
exec xp_logininfo 'domain\groupname'  
go  
exec xp_logininfo 'domain\username'  
go

I get 3 x 'Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" ... '

DB1 has collation of Latin1_General_CI_AS
DB2 has collation of SQL_Latin1_General_CP1_CI_AS

all system databases are Latin1_General_CI_AS

hope this explains a bit more...

If anyone knows of a good security audit tool then thats kind of where I am heading. I have two simple questions:
1 - Who can access this database?
2 - What databases can this user get to?

sql-server sql-server-2005

3 Answers

  1. I actually get the same issue on my Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64). Then I did some testing. I found out if I switch the DB context to master itself, the error does not occur. Next I did a simple trick, Instead of calling: EXEC xp_logininfo @CurrentUser I did: EXEC master.sys.xp_logininfo @CurrentUser and the error did NOT occur. This may work for you as well, even if you are on another version.

    • 4

  2. What version of SQL are you using - I don't get the problem in SQL 2005 with system databases in Latin1_General_CI_AS and active database in SQL_Latin1_General_CP1_CI_AS. Is it specific to a particular version of SQL?

    • 3

  3. Fatherjack,

    Could you provide more details? Are you using any parameters with xp_logininfo? Are you running it inside of each database? Also, what is the end goal (i.e., are you storing the info somewhere, or is this an application that is trying to execute the xp_logininfo?)

    There may be a workaround with some other system procs, but before I suggest anything I need to know more about what it is you are trying to accomplish. hmmm...i guess that is my way of saying "what are the requirements?"

    • 2