Changes to sysusers and sysxlogins in SQL 2008

Question

Hi all,

I am currently updating a MS SQL 2000 server to SQL 2008. One of the issues highlighted by the Upgrade advisor is that the undocumented table sysxlogins has been removed.

I currently have a procedure that is run by a user 'foo' to determine if the user 'bar' exists in the database blah. If the user exists the user's password is compared to the password that was passed in to the procedure in order to determine if bar is allowed to log in to an application, it looks like this:

@UserName Varchar(50),
@Password Varchar(50)

As
Set NoCount On

------------------------------------------------------------------------------------
-- Check username
------------------------------------------------------------------------------------
If Exists
(
   select top 1 name
   from blah.dbo.sysusers With (NoLock)
   where name = @UserName
)
Begin

   ------------------------------------------------------------------------------------
   -- Check Password
   ------------------------------------------------------------------------------------

   If Not Exists
   (
      Select *
      From master.dbo.sysxlogins With (NoLock)
      Where srvid IS NULL
      And name = @Username
      And ( ((@Password is null) or (@Password = '') and password is null)
      Or (pwdcompare(@Password, password, (CASE WHEN xstatus&2048 = 2048 THEN 1 ELSE 0 END)) = 1))
   )
   Begin

      Return 2

   End

   Else

   Begin

      ------------------------------------------------------------------------------------
      -- Check Role
      ------------------------------------------------------------------------------------

      Select usg.name
      From blah.dbo.sysusers usu
      left outer join (blah.dbo.sysmembers mem inner join blah.dbo.sysusers usg on mem.groupuid = usg.uid) on usu.uid = mem.memberuid
      left outer join syslogins  lo on usu.sid = lo.sid
      where usu.name = @Username
  and usg.name not like 'db_%'

      Return 0 -- Username and password correct

   End

End

Else

Begin

      Return 1 -- Username incorrect

End

This all works fine under SQL 2000, yet I must now pay the price of using undocumented system tables and make it work under 2008.

There are two problems with this, the first problem is that foo can no longer see all of the database users when executing:

select * from blah.dbo.sysusers

or Microsoft's recommended alternative:

select * from blah.sys.database_principals

I understand that this is due to the fact that members of the public role no longer have access to object meta data unless they are a member of sysadmin or have the View Definition permission on the object.

It is not possible for foo to be a member of sysadmin, so as far as I understand I need to grant foo the View Definition permission, but on which object? I don't think I do it on the system view, so do I do it on every single user?

Secondly, and similarly, I need to change my reference to sysxlogins to sys.sql_logins. Again foo can only see itself and sa when executing

select * from sys.sql_logins

How can I get foo to see all of the server logins in this list?

There will no doubt be similar problems when accessing sysmembers and syslogins later on in the code but hopefully an understanding of the two examples above will help me to sort the rest out.

Thanks in advance,

Answer 1

You can grant the SELECT right directly on sys.database_principals, as long as the login has a user in the master database. For example:

use master
create user MyUser for login MyUser
grant select on sys.database_principals to MyUser

Then, in SQL Server 2008, passwords are encrypted, even for the administrator. You can, however, verify a password by trying to change it. The change procedure will give an error if the old password is incorrect.

declare @rc int
begin try
    exec @rc = sp_password 'welcome', 'welcome', 'MyUser'
end try
begin catch
    set @rc = ERROR_NUMBER() 
end catch

-- Should be 0 on success
select @rc

For this to work, you have to disable Enforce password policy in the Login Properties dialog. Otherwise, the policy would prevent you from changing your password too often.

Answer 2

I think GRANT SELECT ON... is more troublesome as one have to add the user to the master database. The below was the solution for me:

USE master
GRANT VIEW ANY DEFINITION TO foo

If you have an app that works on various versions of SQL you need to check if the server version is higher then 8 (GRANT VIEW ANY DEFINITION works from SQL 2005 though it seemes not be needed there).