July 25, 2004 at 11:15 pm
hi,
can anyone explain to me what is the idea of sql login? Please correct me if I'm wrong.
a login consists of a login name and password and it is associated with roles. It could have different roles and each role might have different opertaions( permissions) that a user can perform. In addition, a login relates to a databse rather than to the entire sql server group. That is, an individual might logon as some login name, and that would enable him to perform some operations on databse X and NOT on database Y because the roles and permission granted to that user are only with respect to databse X.
Now I'll get to the topic that prompts to approach this sql discussion group. I have a WORKGROUP setup with two machines, machine A and Machine B. In machine A ,I have adatbase which I would like to backup such that the backup file with the .bak extension be created in machine B. When I try to create new backup media device and point to a folder in another machine I am prompted ONLY with GUI corresponfing to the directory of machine A, whereas I'm interested in the directory of machine B, just as is evident when browsing through 'my network places' when using windoes explorer. In order to fix that problem, I was told to change the settings of the MSSQLSERVER service on machine A. The explanations behind this is that it's the service that attempts to logon to the other machines in the WORKGROUP setup, but when the account under which it tries to do so is a local account, it fails to display the file directory system of machine B. Therefore, I need to oprn Windows Service applet and change the account to an accoun that can log on both to machine A and to machine B with identical passwords. It is at this point that I'm getting lost. To be more specific, I dont know where I need to go and create another accout and how to do so, assuming that what I have laid out above is correct. Can anyone give a hardly sql skilled person the exact instaructions what I need to do?
note: i have sql server 2000 enterprise edition running on windows server 2000
thank you in advance
avi
July 26, 2004 at 5:34 am
It's correct that the SQL Server service that holds the database you need to back up should run under an NT-account with local administrator group on server A. The same NT-account should have rights on either on a share on server B or the correct NTFS-rights on server B in order to backup a database to server B.
\\ServerB\ServerABackups
backup mydatabase to disk = '\\ServerB\ServerABackups\mydatabase_backup.BAK'
If you run the backup from QA (Query Analyzer) your NT-login has to have the correct rights on the share on Server B. If it should be a scheduled job in SQL Server Agent, the job has to be owned by either 'sa' or a user that is member of SQL Server Role "System Administrator".
robbac
___the truth is out there___
July 26, 2004 at 12:19 pm
hello robbac,
Thanks for the effort to help and for your tips; i'm getting there but am still annoyed that something that is seemingly simple is so hard to accomplish! I tried to do excatly as you instructed me and i was not successful. I am using the administrator+password when I log in to both machines (sql server 2000 machines), so as you can see logging is done with same name and password. I crerated a share folder as you instructed me. When I try to create in SQL instance (of machine A) a backup device, using the GUI, and I'm prompted with a file browser, I don't see any sign of the existence of the shared folder I created. However, from the windows eplorer of machine A I CAN see the shaed folder. What am I doing wrong? Maybe it's not attainable with the GUI?
thanks
avi
July 26, 2004 at 7:27 pm
OK, I see the issue. You're using Enterprise Manager to create a backup device. If I recall correctly, it only looks at the SQL Server drives, etc.
You can do this using T-SQL.
K. Brian Kelley
@kbriankelley
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply