SQLDMO.SQLRestore erases loginames
-
I have a database MyDB. It has roles Administrators and Employees with members Admins and Emps respectively. My frontend VB app will show all the users of the database. This is done thru the following steps:
1) Query all the users and put the result in a SQLDMO.QueryResults object
2) Create an ADODB.Recordset object with fields UserID, LoginName, and GroupName
3) Populate the the recordset with the contents of the SQLDMO.QueryResults
4) Exclude the sa login thru ADODB.Recordset.Filter property
5) The ADODB.Recordset is used to populate a listbox
The following block is the complete code snippet:
---------------------------------------
Dim myServer As New SQLDMO.SQLServer
Dim qryUsers As SQLDMO.QueryResults
Dim rsUsers As New ADODB.Recordset
' Connect SQLServer
myServer.LoginSecure = True
myServer.Name = "(local)"
myServer.Connect
Set qryUsers = myServer.Databases("MyDB").EnumUsers
Dim i As Integer
' Define recordset fields
rsUsers.Fields.Append "UserID", adInteger
rsUsers.Fields.Append "LoginName", adVarChar, 10
rsUsers.Fields.Append "GroupName", adVarChar, 50
' Populate the recordset
rsUsers.Open
For i = 1 To qryUsers.Rows
----rsUsers.AddNew
----rsUsers!UserID = qryUsers.GetColumnLong(i, 5)
----rsUsers!LoginName = qryUsers.GetColumnString(i, 3)
----rsUsers!GroupName = qryUsers.GetColumnString(i, 2)
----rsUsers.Update
Next i
' Don't include the 'sa' login
rsUsers.Filter = "LoginName <> 'sa'"
' Release objects
myServer.Close
Set myServer = Nothing
------------------------------------------
After a database restoration with SQLDMO.SQLRestore, the loginames of the restored database are changed to blanks except the sa. Worse, the members of the Employees are now denied access from the database.
Only the members of the Administrators role are allowed to perform backup/restore. The following is complete vb code snippet for the restoration:
------------------------------------------
Dim myRestore As New SQLDMO.Restore
myRestore.Files = Text1.Text
myRestore.Database = "MyDB"
' Force database replacement
myRestore.ReplaceDatabase = True
' Coomit restore
myRestore.SQLRestore myServer
-------------------------------------------
What's wrong?
Edited by - bani on 08/08/2002 1:16:19 PM
Edited by - bani on 08/08/2002 1:17:38 PM
"The way to get things done is not to mind who gets the credit for doing them." - Benjamin Howett -
First thought..and not really an answer to your problem...is that you're doing more work than you need to by using DMO and ADO. If you're ultimately going to use the ADO recordset for filtering or binding or whatever, just pull the data from sysusers directly. Alternatively, you could just load into your listbox directly and exclude SA as you go, bypassing the recordset entirely.
Restoring a database doesn't affect logins directly. Users are stored in sysusers and get restored with the database. You can potentially run into a problem if you're restoring a backup from another server - the SID for the login won't match the user in the database, so the userid will be orphaned. Take a look at sp_change_users_login, we have several articles here on the site that cover it.
Andy
-
Sorry it took too long for me to say "Thank you."
I am currently on the process of refactoring especially the part concerning the SQLDMO/ADODB.
"The way to get things done is not to mind who gets the credit for doing them." - Benjamin Howett -
No problem. Let us know if you have more questions!
Andy
-
Interesting thoughts. I know I use the backup and restore method to bring our production database down and then restore it to a local server to a database with a different name. Although the users are erased from the database, because we have a different set of users with access to production, the logins are still on the server. I am able to use SQLDMO to write a script that adds the users back to the database with the appropriate permission.
Bruce Szabo, MCSE+I, MCDBA, MCSD
Bruce Szabo, MCSE+I, MCDBA, MCSD -
Andy, I cannot formulate the exact select statement for fetching the logins of a database together with the roles where they belong using sysusers table. Is it possible to do so?
bmsjr, please take a look at the vb snippet for my database restoration. Maybe you can find something wrong about it that leads to the problem of the logins erasure.
"The way to get things done is not to mind who gets the credit for doing them." - Benjamin Howett -
Everything looks fine to me, unless DBs would not allow logins to be restored with the database which is not my experience. I do some restores between servers to different named databases and I always restore the users to the database. The logins for the server are not erased.
Bruce Szabo, MCSE+I, MCDBA, MCSD
Bruce Szabo, MCSE+I, MCDBA, MCSD -
bmsjr, if you don't mind sir, can you post a vb-dmo code snippet of yours for backing up and restoring database. I just want to learn from experienced DBA's like you and I would really appreciate it.
"The way to get things done is not to mind who gets the credit for doing them." - Benjamin Howett -
I took the code from what Andy had done to do my backups. Things I have tinkered with to make it useful in my situation. I have some code where I take a database with one name and save it to another database name.
I have a script that allows me to kill processes so the database can be loaded at night.
I have another script to add user permissions to the database. The kill script should be listed on the scripts in the site. I will try and remember to post the other ones when I get back in the office.
Bruce Szabo, MCSE+I, MCDBA, MCSD
Bruce Szabo, MCSE+I, MCDBA, MCSD -
I GOT IT!!!
I just forgot to assign value to SQLRestore.Action (see my snippet).
bmsjr,Warren, thanks very much!
"The way to get things done is not to mind who gets the credit for doing them." - Benjamin Howett
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply