Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums  SQL Server 2008 Forums  SQL Server Administration (2008)  Permissons and Logons

Author  Topic  dcbradle Starting Member 1 Post Posted - 2010-11-15 : 09:59:25 Hi, I have SQL Database that I have been using as the backend system for my ASP website for a while now. I have been successfully creating new tables, adding permission to them and then successfully running sql queries from my asp pages. However, today I created a new table, populated it and then tried to run a simple select query on it and then got the error 'Login failed for sa'. Although I was still able to run other queries on other tables successfully. Does anyone have any thoughts on this? I was using 'sa' as the username in my connection string, however, none of my tables had that user in the permission area. For now the way I have got around it, is to change the user in my connection string to 'IUSR_sa' as this is a user listed in the permission area for every table and stored procedure. The problem with this is that, because I have to update the database on a weekly basis with a backup taken from a remote live version of the website, the link between the User 'IUSR_sa' on the database and the Login 'IUSR_sa' on the SQL Server keeps dropping everytime I restore a backup. I am having to run the following script: EXEC SP_Change_Users_Login 'Auto_Fix','ISUR_sa',Null,'123456' So does anyone know of a way of keeping this link when restoring the backup? Is there an option to select when backing up the database. Any help with any of the problems would be most appreciative. Thanks and sorry for the long message. David Bradley