Posted by Erland Sommarskog on 02/24/06 00:52

Kittikun (kittikun@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, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]