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?