July 2, 2013 at 2:36 pm
As I process each month, I am copying some of the tables in my DB (db1) to another DB (ab1_archive)on the same server. This is to allow users access to older data from a web front-end. The tables are renamed from tbl1 to tbl1_201305 - the suffix being year and month.
This is done in a stored proc in db1. It worked once, but is now giving error that either db1_archive does not exist or there is no permission. I am not sure what changed. Some of the tables are getting copied though.
What am I doing wrong? is there a permission on the table that I need to change? If so, how?
Thanks in advance.
BEGIN
if @thruPeriod is null or @thruPeriod = ''
BEGIN
PRINT(N'The value cannot be null or empty.')
END
ELSE
BEGIN
--final output
EXEC ('SELECT * INTO tbl1_' + @thruPeriod +
' FROM tbl1')
END
END
July 2, 2013 at 2:45 pm
The way your statement is written, the tables would be in the same database. You need to qualify them with db and schema:
'SELECT * INTO db1_archive.dbo.tbl1_' + @thruPeriod +
' FROM db1.dbo.tbl1'
July 2, 2013 at 2:58 pm
I am doing USE db1 at the top. so that should not a problem.
July 2, 2013 at 3:01 pm
So it will try to create your archive table in db1, since that's the DB it's operating in. Is that what you want? If your source and your target are in 2 different databases, you have to qualify at least one of them.
July 2, 2013 at 3:09 pm
Sorry no when I changed the table name I deleted too much.
EXEC ('SELECT * INTO db1_Archive.dbo.tbl1_' + @thruPeriod +
' FROM tbl1')
July 2, 2013 at 3:11 pm
Ok, so on to the next possible issue. Does the accout this is running under have permissions to create tables in the archive database? What is the exact error message you're getting?
July 2, 2013 at 3:20 pm
Right now, when I tried I got the following error:
sg 916, Level 14, State 1, Line 1
The server principal <user> is not able to access the database "db1_Archive" under the current security context.
But earlier I was getting an error something like below:
The database "db1_Archive" either does not exist or there is no permission.
So I went ahead and created NT Service/SYSTEM and NT Service/Network service users in the archive db. It did not have nay users except guest and public and dbo.
July 2, 2013 at 3:22 pm
So, you need to either get permissions to create tables granted to the account you're using, or use another account that already has those permissions. Both those messages indicate a lack of ability to access the archive database.
July 2, 2013 at 3:25 pm
So that's the question. Ho wdo I do that?
I am logged into domain. So I should be part of network service*** accounts.
Is that not enough?
Sorry, i am a newbie and learning as i go along.
July 2, 2013 at 3:33 pm
Just logging in to the domain doesn't get you anything in SQL Server. If your login gives you administrator rights on the box SQL Server is running on, you'd have elevated permissions on the SQL Server (unless someone has removed the defaults). That doesn't look like it applies to you. You obviously have a login to the SQL Server, but you have no access to the archive database, so you need to ask whoever handles security to the SQL Server to add you as a user to the archive database with create table permissions (or maybe with database_owner role). If this is a development server, the production permissions may have to be different, so you'll need to work with the person doing your server administration to work out the appropriate permission set for each environment, or the set of accounts you need to use in each environment.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply