2

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?

Improve this question
2
  • I cannot replicate this issue either. When you say all system databases are you checking master,msdb and model (& temp) ?
    – Nick Kavadias
    Jun 2, 2009 at 14:22
  • what do you get when you run: select name,collation_name from sys.databases
    – Nick Kavadias
    Jun 2, 2009 at 14:26

3 Answers 3

Reset to default
4

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.

Improve this answer
3

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?

Improve this answer
4
  • SQL 2005 Standard Edition sp2 on 64bit Windows Server 2003 r2 ... 9.00.3042.00 (X64)
    – Fatherjack
    Jun 2, 2009 at 8:27
  • Weird, I'm using the exact same edition.
    – Richard Gadsden
    Jun 2, 2009 at 9:40
  • This is not an answer, but do not know why it was voted as an answer.
    – jyao
    Nov 27, 2017 at 22:39
  • Because you couldn't comment on questions in 2009.
    – Richard Gadsden
    Nov 27, 2017 at 23:37
2

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?"

Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .