January 3, 2008 at 2:37 am
Hi All,
I have a Database named TEST and all the database object is created under dbo Schema In my Development Server.
When I uploaded the database to Web Server, I found all the TEST Database object is bind under different schema instead of dbo. I found the dbo schema already present there.
Ex: -
---------
Previous : dbo.MainTable
Current : ex@abc.com.MainTable
Here My Question is : if I will create a schema "test" under dbo, then how can i access all the object which is created under dbo.
I created a new schema : test under dbo.
but i am unable to access the Table : dbo.MainTable
why so?
Then how can i use test schema to access all the dbo schema object as per inherit concepts?
Please Help me?
Cheer!
Sandy.
--
January 3, 2008 at 3:44 am
Hi Sandy,
I am a little confused - do you want to create schema test in database TEST?
What do you mean by 'uploaded database to Web Server'? Do you have script that creates all objects? If so, is it possible that this script does not specify schema explicitly, that is
create table MyTable (...) instead of create table dbo.MyTable (...) ? Then, seems user that was mapped to login you used to log in to web server database has default schema ex@abc.com. That's why all objects are created there.
Anyway, you can move objects between schemas using following statement:
alter schema dbo transfer [abc@xyz.com].MyTable
this moves [abc@xyz.com].MyTable to schema dbo, effectively making it dbo.MyTable. Be sure you don't have MyTable in dbo already.
In general, it is a good practice to use two part naming convention, prefixing all object names with schema names. Otherwise you run into troubles easily.
HTH
Piotr
...and your only reply is slàinte mhath
January 3, 2008 at 4:03 am
Hey Piotr,
Thanks,
I got your point,
I was uploaded the Database by running Script only through Ftp Site.
when its show dbo as error I replaced with one space
like this : replace "dbo." " "
then it creates all objects using the respective schema.
I used the script because of my DB backup Size, then I Uploaded all the data by using Script only.
as per you schema can be inherits the permission, let me check and I will get back to you,
I have a small issue, If one Database is placed on remote server and I wants to take back up of this database, its creating the backup file on the remote server where i doesn't have the permission, Is there any way to get BackUp file in local server from remote server database ?
Cheer!
Sandy.
--
January 3, 2008 at 4:35 am
Hey Sandy,
Can you say what error was reported when you used "dbo." prefix? How do you execute script on remote server? How did you copy data to remote database?
Can you connect to that server using SSMS?
Piotr
...and your only reply is slàinte mhath
January 3, 2008 at 5:47 am
Hi Sandy,
When you run backups, don't use drive paths like this:
BACKUP DATABASE x TO DISK = 'C:\x.bak'
Because it will place them on the relative drive for that server. Instead, use machine names and a share:
BACKUP DATABASE x TO DISK = '\\MachineICanAccess\ShareName\x.bak'This way it goes to a place you have access to.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 3, 2008 at 10:56 pm
Hey Grant,
I used your query like this :
BACKUP DATABASE TEST TO DISK = '\\111.111.1.111\E\abc.bak'
Its Showing error like this.
Msg 3201, Level 16, State 1, Line 1
Cannot open backup device '\\111.111.1.111\E\abc.bak'. Operating system error 5(Access is denied.).
Msg 3013, Level 16, State 1, Line 1
BACKUP DATABASE is terminating abnormally.
Any Idea?
Cheer!
Sandy.
--
January 4, 2008 at 5:33 am
It has to be permissions. Just what it says. Remember, the backup is not running as you, it's running as the account that SQL Server runs under. So you have to backup to a place that you both have access to if you want to get to the file later to move it around. If you just want to run backups & restores, all that matters is that the SQL Server service account has access. Make sense?
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 4, 2008 at 6:40 am
Do you have write permissions to the location \\111.111.1.111\E\? E is drive E: ? Why not setup a share location with explicit permissions for the user this job is running as?
January 4, 2008 at 11:30 pm
Hi All,
I have all the permission, because i am login by using "sa"
as my login to SQL Server 2005 and the Share I am using is my own System and i gave full permission to that directory,
Then why i am unable to take backup?
Cheers!
Sandy.
--
January 5, 2008 at 6:02 am
Is "E" a drive or a share name?
It has to be permission issue from the service account that SQL Server runs under. Not the 'sa' login, but the login that is defined in the Windows Services list for SQL Server.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 5, 2008 at 8:56 am
So probably it's the 'local system' account that wants to access this share. and local system does not have network credentials. maybe you could set up an FTP server on that server? or share the server folder you store backups on so you can reach it from your file manager?
...and your only reply is slàinte mhath
January 6, 2008 at 10:03 pm
Hey Grant,
"\E" is share drive which have all the permission.
and can you please explain me something about as you written in above post
service account that SQL Server runs under. Not the 'sa' login, but the login that is defined in the Windows Services list for SQL Server.
I would be happy to know more about this.
hey Piotr,
i am not clear what you trying to say, more clear to you, I am using system from my office network not any personal PC, and I have all the permission to access the network computers.
As per both of you I checked all but still no solution?
Cheer!
Sandy.
--
January 6, 2008 at 10:52 pm
Sandy,
Open up configuration manager on the server and you will see the list of SQL services, and the accounts that each of them are running under. Because backups are run using the service account credentials, it doesn't matter what rights your id has, or what id you are logging on as - you will need to grant rights on your shared folder to the service account. If you are on a domain, you may want to consider running SQL under a domain account with minimal rights.
January 6, 2008 at 11:39 pm
Hey Matt,
Can you be specific?
Open up configuration manager on the server and you will see the list of SQL services, and the accounts that each of them are running under. Because backups are run using the service account credentials, it doesn't matter what rights your id has, or what id you are logging on as - you will need to grant rights on your shared folder to the service account. If you are on a domain, you may want to consider running SQL under a domain account with minimal rights.
1st Qst: Which service are you talking?
2nd Qst: Should I check in the Remote Server or in my Local System?
3rd Qst: what exactly service account mean?
Cheers!
Sandy.
--
January 7, 2008 at 6:01 am
Inside the SQL Server 2005 folder is a folder called Configuration Tools. Inside there is a tool called SQL Server Configuration Manager. Open that up. In there on the left side of the screen is an icon and the label SQL Server 2005 Services. Click on that. On the right side of the screen you should see the list of SQL Server Services. Right click on SQL Server and select Properties.
This will open a window that has "Log On As:" at the top. Whatever is configured here is the service account. This account has to have permissions to the drives that you want to use for backups, etc. If it's running as the local service account, then your local service account doesn't have access to the drive. You can set up an appropriate account and then give it access. That's better than giving the local service account network privileges (hackers & all that).
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 15 posts - 1 through 15 (of 24 total)
You must be logged in to reply to this topic. Login to reply