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

    Tuesday, March 05, 2013 7:24 AM

Answers

  • 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
    Tuesday, March 05, 2013 7:30 PM

All replies

  • 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

    Tuesday, March 05, 2013 10:46 AM
  • 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
    Tuesday, March 05, 2013 7:30 PM
  • 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

    Tuesday, March 05, 2013 9:31 PM