Answered by:

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

Question

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).

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