• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1044
  • Last Modified:

How to restore database permissions

We are moving a database from SQL Server 2000 to 2005.  The database seens to to restore OK, but when we run the web application we get numerous errors like "Username does not have select permission on tablename" or "execute permission denied on object storedprocedure1", etc. and we have to manually reset the permissions on every table, view and stored procedure.  How can we restore the database, including all permissions on tables, stored procedures, etc?
0
medcare
Asked:
medcare
2 Solutions
 
Scott PletcherSenior DBACommented:
The users and their permissions *are* still there.  The problem is that the user is not sync'ed up with the login.

Run the code below in Query Analyzer and then run the commands it generates.  That should re-sync the users.

SELECT 'EXEC sp_change_users_login ''UPDATE_ONE'', ''' + su.name + ''', ''' + su.name + ''''
FROM sysusers su
LEFT OUTER JOIN master..syslogins sl ON su.sid = sl.sid
WHERE su.issqluser = 1 AND su.name NOT IN ('guest', 'INFORMATION_SCHEMA')
AND sl.sid IS NULL
0
 
Eugene ZCommented:
This problem is known as an orphaned user
http://support.microsoft.com/kb/314546
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now