Tuesday, March 05, 2013 7:24 AM
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 10:46 AM
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 7:30 PM
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 >= 16384RLF
Tuesday, March 05, 2013 9:31 PMThe 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