Logins to the website doesnt work after DB backing up and restoring

Hi all, plz help...

I have an asp.net login control on my website;

my users are stored in a SQL DB;

The problem is when I back-up my DB from my PC and restore it to my server; the logins doesn't work anymore. I mean the user I've created on my PC can't login on the website on the internet...

Thanks a lot...

I've had this same problem. If you delete the user from the database and re-add it, it will work. Does anyone know why this happens? Is there a numeric ID the server uses for the user name?|||

Thank god someone else had the same problem; it's been so long that I'm working on it that I allways thought it only happens to me..

Yes if I delete and then re-register same user it works but I can't say to my users please re-register:))

is there any way to programmaticaly do it? or any REAL solution??

How did you handle that? I mean does it work now?

|||Wait, when you say they are stored in a sql db, do you mean they are stored in a table or they are sql logins of the database? This happened to me when I had a user with a sql login.|||Backup/Restore does not transfer the logins. You need to generate the script to create the logins and run it to set the permissions after your restore.|||

They are in a table..

I'm not talking about SQL logins. Logins are aspnetdb logins for a web site. I mean users inside the aspnetdb_Membership and aspnetdb_Users tables...

|||The users may be in a table. You still need to run a script to SET the permissions for the users.|||

What script s that?

Users (I mean the user connecting by internet) already got permissions and re-mapped?

|||

The user exists in your table but he/she does not yet have the permissions to execute any scripts.

Check out BOL for sp_addlogin, sp_grantlogin, sp_defaultdb, etc.. If you are not sure of the scripts, try generating the script for existing logins.

|||

Hi,

I think the answer is hidden somewhere over there I didn't even realised that there could be that much stored procedure and functions... Anyways I found that sp's but I didn't understand anything:) how can I work em out from a query? Also I found "dt_validateloginparams" could that also be related?

Actually theres a parameter HasAccess like below:

EXEC

%%LocalLogin(Name= @.loginame).SetAccess(HasAccess= 1)

I think this is the key line but where does it store this HasAccess info? I couldn't find it in the tables?

Thanks a lot...

|||

In Enterprise Manager, right click on the DB -> All Tasks -> Generate Scripts -> Options tab -> select "Script SQL Server Logins" -> Back to General tab -> Click on Preview.

You can either paste the code into a stored proc and call it from your application or parameterize the scripts and use it for any login...as creative as you can get...

|||

Hi;

I'm sorry; I'm as lame as I couldn't figure it out. Maybe you already told me the answer but I don't know...

Could you please tell me what to do:)

Thanks a lot..

|||I just did. Thats the best I can do.|||Ok man; thanks.. I'll try..|||http://weblogs.asp.net/scottgu/archive/2006/04/22/Always-set-the-_2200_applicationName_2200_-property-when-configuring-ASP.NET-2.0-Membership-and-other-Providers.aspx

Logins dont work after restoring on backup server

I am doing a complete backup on a sql 7 db and then doing a complete
restore (with overwrite existing db) on a sql 2000 server. This is now
our hot standby server. I have the process automated and it works
great. The only problem I have now is the logins dont work.

I have tried running EXEC sp_change_users_login 'Report', and the
logins appear.
However, when I run EXEC sp_helplogins 'joe', the results are empty.

So, I am guessing all I need is a sp that will re-associate my logins
with the correct db and grant the appropriate permissions.

If anyone has any ideas that would be great.

I have also considered doing a log ship instead of a full backup and
restore. Does anyone have any suggections or good examples of how to
make that happen?

ThanxUzytkownik "Dave" <funkdm1@.yahoo.com> napisal w wiadomosci
news:f5174e0f.0307110451.5a75ae07@.posting.google.c om...
> I am doing a complete backup on a sql 7 db and then doing a complete
> restore (with overwrite existing db) on a sql 2000 server. This is now
> our hot standby server. I have the process automated and it works
> great. The only problem I have now is the logins dont work.

O course, because they are stored in master-->sysxlogins table.
You have only database users in your database, but they are not connected
with logins.

> I have tried running EXEC sp_change_users_login 'Report', and the
> logins appear.
> However, when I run EXEC sp_helplogins 'joe', the results are empty.

O fourse, becaues sp _helplogins provides information about logins and the
associated users in each database, but they are not exist, right (its not
the same database master)

> So, I am guessing all I need is a sp that will re-associate my logins
> with the correct db and grant the appropriate permissions.

Use sp_change_users procedure with Auto_Fix option (but be cousious. Why?
See BOL ;)

> If anyone has any ideas that would be great.

I hope it will be enough.

> I have also considered doing a log ship instead of a full backup and
> restore. Does anyone have any suggections or good examples of how to
> make that happen?
Hmm? Can you specify? I don tunderstand what is you problem (sorry, probably
my english is not enough:(

regards
Marcin D

logins after a restore

Ive always dropped/ recreated the SQL Users after restoring a backup from
prodiuction to dev in order to make the Users usable after this scenario
occurs. Is there a better way?
TIA, ChrisR
Check out sp_change_users_login in the BOL.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
..
"ChrisR" <noemail@.bla.com> wrote in message
news:uW9LcMjOFHA.904@.tk2msftngp13.phx.gbl...
Ive always dropped/ recreated the SQL Users after restoring a backup from
prodiuction to dev in order to make the Users usable after this scenario
occurs. Is there a better way?
TIA, ChrisR
|||Thanks Tom.
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:eUA2UajOFHA.2604@.TK2MSFTNGP10.phx.gbl...
> Check out sp_change_users_login in the BOL.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinnaclepublishing.com
> .
> "ChrisR" <noemail@.bla.com> wrote in message
> news:uW9LcMjOFHA.904@.tk2msftngp13.phx.gbl...
> Ive always dropped/ recreated the SQL Users after restoring a backup from
> prodiuction to dev in order to make the Users usable after this scenario
> occurs. Is there a better way?
> TIA, ChrisR
>

logins after a restore

Ive always dropped/ recreated the SQL Users after restoring a backup from
prodiuction to dev in order to make the Users usable after this scenario
occurs. Is there a better way?
TIA, ChrisRCheck out sp_change_users_login in the BOL.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
.
"ChrisR" <noemail@.bla.com> wrote in message
news:uW9LcMjOFHA.904@.tk2msftngp13.phx.gbl...
Ive always dropped/ recreated the SQL Users after restoring a backup from
prodiuction to dev in order to make the Users usable after this scenario
occurs. Is there a better way?
TIA, ChrisR|||Thanks Tom.
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:eUA2UajOFHA.2604@.TK2MSFTNGP10.phx.gbl...
> Check out sp_change_users_login in the BOL.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinnaclepublishing.com
> .
> "ChrisR" <noemail@.bla.com> wrote in message
> news:uW9LcMjOFHA.904@.tk2msftngp13.phx.gbl...
> Ive always dropped/ recreated the SQL Users after restoring a backup from
> prodiuction to dev in order to make the Users usable after this scenario
> occurs. Is there a better way?
> TIA, ChrisR
>

logins after a restore

Ive always dropped/ recreated the SQL Users after restoring a backup from
prodiuction to dev in order to make the Users usable after this scenario
occurs. Is there a better way?
TIA, ChrisRCheck out sp_change_users_login in the BOL.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
.
"ChrisR" <noemail@.bla.com> wrote in message
news:uW9LcMjOFHA.904@.tk2msftngp13.phx.gbl...
Ive always dropped/ recreated the SQL Users after restoring a backup from
prodiuction to dev in order to make the Users usable after this scenario
occurs. Is there a better way?
TIA, ChrisR|||Thanks Tom.
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:eUA2UajOFHA.2604@.TK2MSFTNGP10.phx.gbl...
> Check out sp_change_users_login in the BOL.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinnaclepublishing.com
> .
> "ChrisR" <noemail@.bla.com> wrote in message
> news:uW9LcMjOFHA.904@.tk2msftngp13.phx.gbl...
> Ive always dropped/ recreated the SQL Users after restoring a backup from
> prodiuction to dev in order to make the Users usable after this scenario
> occurs. Is there a better way?
> TIA, ChrisR
>

Login vs. Userid

When I restore a database from the production environment to the
test environment without restoring master, I break the link
between master..syslogins and each userid in the databases sysusers table. (I cannot restore master as multiple instances of unrelated databases exist on the dataserver)

Can I update the userid's SID entry in the sysusers table to match
what is currently listed for the related login in master's syslogins table,
or are there other columns that are used to create the link between the
userid and login id?You can try update sids but my advice - recreate all logins with sids from your production server:

sp_addlogin [ @.loginame = ] 'login'
[ , [ @.passwd = ] 'password' ]
[ , [ @.defdb = ] 'database' ]
[ , [ @.deflanguage = ] 'language' ]
[ , [ @.sid = ] sid ]
[ , [ @.encryptopt = ] 'encryption_option' ]|||Is it to be assumed that your solution is counting on the Login being
exclusive to the userid being restored?|||This page will help

http://support.microsoft.com/default.aspx?scid=kb;EN-US;q246133|||Create all logins and than restore dbs.|||snail: have to reference rhigdon's link and disagree with you, because this would have worked in 6.5, not in 7.0/2K case, because userid is referencing the SID, not the name field in syslogins.|||During creating logins it needs to set SIDs from production server...
See my first post.

Originally posted by rdjabarov
snail: have to reference rhigdon's link and disagree with you, because this would have worked in 6.5, not in 7.0/2K case, because userid is referencing the SID, not the name field in syslogins.|||rocket39,

I have a TSQL script for realligning user ids at my home. I'll post it for you this evening.

blindman|||I think rhigdon's link contains all the necessary scripts to do just that.|||There is a slight difference, in that my code realligns existing user logins for a given database to match those for the server.

Not as extensive as the code in the link, but usefull if you can't create a particular user under the same ID as another server because the ID is being used, or if there are objects that belong to the user and you don't want to have to drop and recreate them before synchronizing IDs.

blindman|||There is also sp_change_users_login to do sid alignments.|||Those Microsoft bastards stole my code!

Know any good lawyers?

blindman

Login problem after restoring DB

Dear Masters;

I have a classical aspnetdb Login DB; When I backup the DB and make some changes and then restore again; users can't login anymore...

I can see this is good for security but what should I do to do it in a propar way?

Thanks...

Hi Kaan,

In the SQL Server, the logins are not contained in the database. So when you backup the DB, you just backup the users.

However the mapping from a login to user is lost. This is by design. In this case, you have to re-map the logins and users after restoring.

HTH. If this does not answer your question, please feel free to mark the post as Not Answered and we will look into it again. Thanks!

|||

Hi Kevin,

Thank you very much this is actually the answer:) but also a trigger of another question:) How can I re-map the logins?

Thanks...

|||

Hi Kaan,

Here are the steps for User Mapping.

1. In the Management Studio, Open Security/Logins and double click on a login to open the Login Properties dialog box.
2. Select User Mapping from the left tree, and you will see mapping on the right.

|||

Hi Kevin,

Thank you very much... Sorry I'm too lamer

I've seen that my database's mapping check box was unchecked; so re-mapping should be simply checking it:)

I think it'll work or at least I'll dicover..

Thanks a lot again.

|||

Q: "I have a classical aspnetdb Login DB; When I backup the DB and make some changes and then restore again; users can't login anymore..."

A: "In the SQL Server, the logins are not contained in the database. So when you backup the DB, you just backup the users.

However the mapping from a login to user is lost. This is by design. In this case, you have to re-map the logins and users after restoring.

Here are the steps for User Mapping.

1. In the Management Studio, Open Security/Logins and double click on a login to open the Login Properties dialog box.
2. Select User Mapping from the left tree, and you will see mapping on the right." (Kevin Yu)

login problem after restoring a database in SQL7

Since the database server was removed to a new Windows Advanced Server 2000, I restored a database from the backup file. When I tried to create a new Login under Security, I got the following error message:

Error 15023: User or role 'MyDatabaseLoginName' already exists in current database.

But I checked it and it doesn't exist. I even could not create a system DSN without the Login. Someone told me to create a new one with another name, but I do not want to change the connection string in my ASP files. Does anyone has any idea about it? Thanks in advance.Did you try to fix it with this system stored proc, can be used in sql server login, should fit ur situation.

sp_change_users_login

login names are deleted after restoring the backups

hi all
i have taken backup of a database 'XYZ' and restored it to a different
server with the same database name 'XYZ', it restored all the items/objects
successfully but the users of that database had blank loginnames in the
restored database. now it makes the objects of that user unaccessable
(without the login name the user cannot access its tables in the database).
i have also tried to make the users before database restoration but when the
database backup is restored it eliminates the user loginname
i'll be thankyou if anyone has the solution
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200508/1HI,
Tranfer all the logins from the mainserver to ur restored database from
dts.
or restore the master database backup
from
sufian|||You need to remap the user in the database to the logins on the other server
(assuming they exist). I tend to prefer to use sp_SidMap for this task which
can be found in the MapSids link in the following KB article
HOW TO: Resolve Permission Issues When You Move a Database Between Servers
That Are Running SQL Server
http://support.microsoft.com/Default.aspx?id=240872
--
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Daniyal khan via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:526E5BAE50074@.SQLMonster.com...
> hi all
> i have taken backup of a database 'XYZ' and restored it to a different
> server with the same database name 'XYZ', it restored all the
> items/objects
> successfully but the users of that database had blank loginnames in the
> restored database. now it makes the objects of that user unaccessable
> (without the login name the user cannot access its tables in the
> database).
> i have also tried to make the users before database restoration but when
> the
> database backup is restored it eliminates the user loginname
> i'll be thankyou if anyone has the solution
>
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200508/1

login names are deleted after restoring the backups

hi all
i have taken backup of a database 'XYZ' and restored it to a different
server with the same database name 'XYZ', it restored all the items/objects
successfully but the users of that database had blank loginnames in the
restored database. now it makes the objects of that user unaccessable
(without the login name the user cannot access its tables in the database).
i have also tried to make the users before database restoration but when the
database backup is restored it eliminates the user loginname
i'll be thankyou if anyone has the solution
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums...erver/200508/1
HI,
Tranfer all the logins from the mainserver to ur restored database from
dts.
or restore the master database backup
from
sufian
|||You need to remap the user in the database to the logins on the other server
(assuming they exist). I tend to prefer to use sp_SidMap for this task which
can be found in the MapSids link in the following KB article
HOW TO: Resolve Permission Issues When You Move a Database Between Servers
That Are Running SQL Server
http://support.microsoft.com/Default.aspx?id=240872
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Daniyal khan via droptable.com" <forum@.droptable.com> wrote in message
news:526E5BAE50074@.droptable.com...
> hi all
> i have taken backup of a database 'XYZ' and restored it to a different
> server with the same database name 'XYZ', it restored all the
> items/objects
> successfully but the users of that database had blank loginnames in the
> restored database. now it makes the objects of that user unaccessable
> (without the login name the user cannot access its tables in the
> database).
> i have also tried to make the users before database restoration but when
> the
> database backup is restored it eliminates the user loginname
> i'll be thankyou if anyone has the solution
>
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forums...erver/200508/1

login names are deleted after restoring the backups

hi all
i have taken backup of a database 'XYZ' and restored it to a different
server with the same database name 'XYZ', it restored all the items/objects
successfully but the users of that database had blank loginnames in the
restored database. now it makes the objects of that user unaccessable
(without the login name the user cannot access its tables in the database).
i have also tried to make the users before database restoration but when the
database backup is restored it eliminates the user loginname
i'll be thankyou if anyone has the solution
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200508/1HI,
Tranfer all the logins from the mainserver to ur restored database from
dts.
or restore the master database backup
from
sufian|||You need to remap the user in the database to the logins on the other server
(assuming they exist). I tend to prefer to use sp_SidMap for this task which
can be found in the MapSids link in the following KB article
HOW TO: Resolve Permission Issues When You Move a Database Between Servers
That Are Running SQL Server
http://support.microsoft.com/Default.aspx?id=240872
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Daniyal khan via droptable.com" <forum@.droptable.com> wrote in message
news:526E5BAE50074@.droptable.com...
> hi all
> i have taken backup of a database 'XYZ' and restored it to a different
> server with the same database name 'XYZ', it restored all the
> items/objects
> successfully but the users of that database had blank loginnames in the
> restored database. now it makes the objects of that user unaccessable
> (without the login name the user cannot access its tables in the
> database).
> i have also tried to make the users before database restoration but when
> the
> database backup is restored it eliminates the user loginname
> i'll be thankyou if anyone has the solution
>
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forum...server/200508/1