Backup users

[Kittikun]

I am currently using the BACKUP DATABASE method to backup my database.
Everything works fine, except for users. I created various users with
sp_addlogin to access this database and they are located in the xlogins
table. The problem is for example, if I backup, remove some users and
do a restore, i won't have them back because i just backup the
database.

So the question is, how can I backup specific users and restore them ?
Do I have to do it manually ? It seems I can't alter the xlogin table
so I'm pretty confused.

[Erland Sommarskog]

Kittikun (ki******@gmail.com) writes:

I am currently using the BACKUP DATABASE method to backup my database.
Everything works fine, except for users. I created various users with
sp_addlogin to access this database and they are located in the xlogins
table. The problem is for example, if I backup, remove some users and
do a restore, i won't have them back because i just backup the
database.

So the question is, how can I backup specific users and restore them ?
Do I have to do it manually ? It seems I can't alter the xlogin table
so I'm pretty confused.

Not really sure that I understand your question. If you decide to drop
a login from the server, you have probably decided to do for good. Why
would you have thst login back if you reture a database?

If you restore the database on a different server, then there is a problem,
because the users in the database will not match the logins. In this case
you can use sp_change_users_login to map users back to logins. For details
on this procedure, please look in Books Online.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[Kittikun]

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...

[Erland Sommarskog]

Kittikun (ki******@gmail.com) writes:

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.
First permit me to straighten up some terminology.

The entity that connects to SQL Server is a *login* (or in SQL 2005
speak a "server principal".)

A login can be given access to a database, and is then mapped to
*user* in that database. ("database principal" in SQL 2005.)

When you take a backup of the database, you will get the *users*,
because the users are stored in the database, more precisely in
the sysusers table.

Normally, a login "joe" maps to a user "joe" in a database, but
there is law about this. A very exceptions is when the login owns the
database. In this case the login's user in the database is "dBo".

Anyway, what I still don't understand is why you remove these logins,
if you think you may need them again.

But if you need to recreate them you can. The fact that you don't know
the password is not an issue - just invent one. Once you have the
login, you can use sp_change_users_login. (And there is no reason to
use sp_adduser.) Of course, if the dropped login what to retain their
passwords, they can't do that.
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.

syslogins is a view, the table is sysxlogins. But I have no idea
whether it works to do this. It certainly isn't supported.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[Kittikun]

Well I think I will give up this feature. Thank you for all you replies.