Problem adding users to server database using Script
-
Hi,
I have an issue regarding adding users to a database - I don't understand why the behaviour is different between a test PC and the server.
We are trying to move the database to a machine on our new network. I have taken a backup of the database, and generated a SQL script from the database (ticking the Script Database Users and Database Roles, Script SQL Server logins, and Script object level permissions flags).
When I restore the database to our clean test machine (running Windows XP Pro and SQL Server 2000), it works perfectly. Running the SQL Script against this database adds users to both the Security->Logins view, and the Database-~>Users wiew The application can then access this database and works correctly.
However, when I do exactly the same on our Windows 2003 server running Windows 2000 Standard Edition with processor licence, the behaviour is different. The users are added to the database server (Security->logins), but not to the database itself.
Can anyone shed some light on what is happening? Thanks very much!
-
When you say that you restore the database to your test machine.... where does that backup file originate from? The same test machine?
Anyway, sounds like you might be getting orphaned users in the database that you're restoring.
The sysusers table in your restored database points to the syslogins table (which is master). Even if you create the logins with the same names, they'll all have different SIDs.
Assuming that you have created all of the logins correctly, what you'll need to do is run this from QA for each user name:
use <name of restored database>
go
exec sp_change_users_login 'Auto_Fix','<user name>'
There's no need to script the database users or the object level permissions because they are held in the database backup, which you restore.
Hope that helps,
-
Thanks Karl for your reply.
I don't think that is the issue. The script re-links the users with the SIDs in the database.
The database backup and script have come from the live CRM system on another server. I have a test PC and the target server sitting on our new network. However, I see no reason why the network should affect anything, as I am not trying to use integrated logins.
Therefore, the process works perfectly on one clean machine but not on the other (the server). I just don't understand why the different behaviour occurs.
-
As Karl said there's no need to script out users, permissions etc., normal steps should be:
1. script out logins
2.restore database to the server
3.run script to create logins
4.synchronize logins and users, i use the following script:
use specify_db
DECLARE @UserName nvarchar(255)
DECLARE orphanuser_cur cursor for
SELECT UserName = name
FROM sysusers
WHERE issqluser = 1 and (sid is not null and sid <> 0x0) and suser_sname(sid) is null
ORDER BY name --no corresponding sid in sysxlogins
OPEN orphanuser_cur
FETCH NEXT FROM orphanuser_cur INTO @UserName
WHILE (@@fetch_status = 0)
BEGIN
PRINT @UserName + ' user name being resynced'
EXEC sp_change_users_login 'Update_one', @UserName, @UserName
FETCH NEXT FROM orphanuser_cur INTO @UserName
END
CLOSE orphanuser_cur
DEALLOCATE orphanuser_cur
go
If you follow these steps, everything should be ok, first of all try to login to the server and access db through osql or query analyzer.
-
Thanks Martin and Karl for your help
I will try it.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply