August 4, 2011 at 7:50 am
We are in the middle of a data center migration project and SQL server is now refusing to allow bulk inserts unless we run the windows service account as Sys Admin.
The environment was build and tested using our templated and automated build process. This was comprehensively tested and passed with flying colours, including bulk imports.
The database was then overwritten with a copy of our production database, the scripted database permissions reapplied and now bulk import fails.
Is it possible that there is a disconnect between server level and database level permissions? Here's as far I have been able to progress.
To remove as much of the complexity as I can, I have created an SQL login, granted this login BulkAdmin rights and is db_owner on the database. I am running the command as taken from the trace using execute as.
Using execute as user x (database permission), it fails as the account does not have rights to run a bulk admin. Using execute as login x (server permission), the insert into log table within the sproc fails as it does not have insert permissions into a database is it db_owner off.
The server is Win 2008R2 64 bit, clustered. the SQL server is 2008 64 bit, SP1, CU10 + a hotfix which gives a build of 10.0.2800. The database is in simple recovery, is a replication publisher.
If you need any further information, please let me know and I'll see what I can make available within the company confidential constraints.
Many thanks in advance.
August 5, 2011 at 5:21 am
Mark,
is this a SQL login or Windows login?
I suspect it's SQL, so did you check if the user and the login are linked together? If not you can use sp_change_users_login to fix it.
[font="Verdana"]Markus Bohse[/font]
August 5, 2011 at 5:56 am
I thought the same thing as Markus, that is was a mapping issue between user and login since you mentioned overwriting the database and scripting the users/logins.
sp_change_users_login will work, but know that it is a deprecated feature replaced by
ALTER USER userName
WITH <set_item> [ ,...n ]
<set_item> ::=
NAME = newUserName
| DEFAULT_SCHEMA = schemaName
| LOGIN = loginName
so to remap the user, it would be
ALTER USER <databaseUserName>
WITH LOGIN = <serverLoginName>
August 9, 2011 at 9:22 am
Many thanks for the feedback.
The user is indeed an SQL one, this was created as a test user after the database had been restored. This was an attempt to remove as many variables as possible.
At first I thought it may be a mapping problem with the original windows login but sp_change_users_login reported no such problems.
I may open a report with MS is I cannot resolve this soon.
Again, many thanks.
Regards, Mark.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply