Sp_helpuser 'username' not returing all the permissions for that user? SQL2000

Question

Version: SQL 2000

When I check the properties of a user\login through enterprise manager I see that it has DB_datareader on a database and it is also mapped to a user defined role. But when I execute sp_helpuser 'username' (or sp_helplogins 'loginname') under that database, it shows that the user has permissions to the user defined role but not DB_Datareader. I have tried several scripts which just shows that the user permissions to user defined role but not db_datareader.

However the user is able to perform selects on all the tables with in that database indicating that it has permissions  to read the database.

The problem is, I am attempting login transfer from SQL 2000 to SQL2008 and the scripts are unable to capture the db_datareader permissions for that user and many others. (method 2: http://support.microsoft.com/kb/246133?wa=wsignin1.0).

As anyone faced above issue? how to resolve this?

Answer 1

Hi Pradeep

does the user and its login have the same uid?

you may need to run:

EXEC sp_change_users_login 'Auto_Fix', 'username'

Hope this helps

Answer 2

You are setting up role memberships in the db_datareader case, not direct permissions.  A script for SQL Server 2000 to prepare the statements to run in your new server.

SELECT 'EXECUTE sp_AddRoleMember ''' + roles.name + ''', ''' + users.name + ''''
 from sysusers users
  inner join sysmembers AS link
   on users.uid = link.memberuid 
  inner join sysusers roles
   on roles.uid = link.groupuid
where users.uid < 16384
  and roles.uid >= 16384

RLF

Answer 3

The permissions of db_datareader (and the other fixed server roles and fixed database roles) are fixed. You can't change them. And there is no need to produce a list of them, because they are always the same. They permissions are listed in Database Engine Fixed Server and Fixed Database Roles

---

Rick Byham, Microsoft, SQL Server Books Online, Implies no warranty