Re: Any Help Please

Ok then the issue should be easy to resolve. Go to the machine hosting the SQL Server 2000 instance. Open up Enterprise Manager and navigate in the tree on the left to the database in question. Right-click and select All Tasks. Select "Generate SQL Script". Now you get a dialog box. There on the General tab click Show All. Then check the "Script all objects" checkbox. Move to the "Formatting" tab and make sure all check boxes are unchecked. Now move on to the "Options" tab and check "Script all object-level permissions". Then click OK to generate the script. This way you get a script of all object-level permissions which you can run against the SQL Server 2005 database.

Jonathan



DSM_DBA wrote:
Thanks Jonathan for your response, however, I have re-synched the SIDs and the problem is that only sporatic object level permissions are lost, it's not an all or nothing situation. While the object level permissions are at the database level and should be restored with the database ( given the SIDs have been reset and they have ).
Thanks and if you have any other ideas, please let me know.


"Jonathan Psaila-Depasquale" wrote:

Hi,

The problem is probably that the original SID of the login has not been transferred, so that your database user accounts are getting orphaned. So in fact when you login as User1, even though User1 exists as a user in the database, the login User1 and the user User1 are not related (or mapped to each other). You should consult this document in order to complete the process properly - http://support.microsoft.com/kb/246133.

Jonathan



DSM_DBA wrote:
Hi, I accidentally posted this as a comment earlier rather than a question...

I'm moving databaes from 2000 to 2005 via either detach/attach or backup/restore and object level permissions being lost.
I had first moved the logins and that seemed to work fine then upon restoring the databases and leaving them at 8.0 compatibility ( which is the requirement of the application ) the explicit object permissions are not restored.
There must be something obvious staring me in the face, but I don't see it.
Any help or guidance would be appreciated.
Thanks,


.

Relevant Pages

  • Re: Cannot Open SQL Server Table in Access.ADP File.
    ... Other possiblities would be that you didn't refresh the database window ... I have an SQL Server 2005 database which functions properly with my ... the Generate Script Wizard to recreate the SQL Server Database. ... Then, If I attempt to open another table, it opens. ...
    (microsoft.public.access.adp.sqlserver)
  • Re: SQL Security
    ... except I'm having problems making it work in a script. ... ;Set properties of DB objects and open connection to database ... > from Books Online (within the SQL Server program group): ... > communicate with SQL Server. ...
    (microsoft.public.sqlserver.server)
  • Re: Connecting to SQL Server Express 2005
    ... Microsoft KB (which was referenced in the very useful DataBase Journal ... generated script against my test database. ... The SQL server produced the following list of errors: ... Line 2: Incorrect syntax near 'GO'. ...
    (microsoft.public.sqlserver.connect)
  • RE: Project Server 2003
    ... when I got to the run script step, I ran the script from the copied database ... I was also wondering about the create database ... For example, type Project Server, and then click OK. ... In the left pane of SQL Server Management Studio, expand Databases, ...
    (microsoft.public.windows.server.sbs)
  • Re: Database Access
    ... Do you create the group in SQL Server on the NT Domain? ... >> from a client script to ... > the database server would not be possible from client machines. ... You will want the sqloledb connection string. ...
    (microsoft.public.scripting.jscript)