Posted by Kittikun on 02/23/06 13:41

Well, I am making an application that allow users to create new users
with different rights and privileges. Each new user have an sql user
account in the database and are registered in a custom table.

So when I make a backup, the custom table will contains all the logins.
If after I remove some users with my application, the sql user will be
removed and the table will be updated. But, if I restore the backup,
the table will contain the deleted users and the sql accounts won't
exist so they won't be usable.

I don't know the clear password so I can't use sp_change_users_login or
sp_add_user.

So to backup/restore sql user account I tried the following thing

1) Make a copy of my users with

use test
go

SELECT * INTO x FROM master.dbo.syslogins

2) To restore them afterward

use master
go

exec sp_configure 'allow updates', 1
RECONFIGURE WITH OVERRIDE
go

insert syslogins select * from test.dbo.x
exec sp_configure 'allow updates', 0
RECONFIGURE WITH OVERRIDE
go

Thought it may have been a good idea but I get an "Update or insert of
view or function 'syslogins' failed because it contains a derived or
constant field." error.

Maybe I am looking on the wrong path...

[Back to original message]