SQL Server Bulk Insert Issue on Windows7

Question

Hi, I’ve got an application that uses SQL Server Express 2005 SP3. This application is distributed to several hundred users, each of whom is running XP. However, our company will be moving to Windows7 soon. My application uses the bulk insert operation in SQL Server, and it runs fine in XP. However, in Windows7 I need to open SQL Server Management Studio, as Administrator, and execute the following command in order to get the bulk insert operation to run properly:

EXEC master..sp_addsrvrolemember @loginame = N'BUILTIN\Users', @rolename = N'bulkadmin'

Obviously it is impractical to do this manually on each user’s PC. So, I’m wondering how I can script this if the users aren’t running my application as administrator. Is it something that can be set in the SQL Server bootstrap package during installation? What about the existing installs?

Answer 1

You have several hundred installations of SQL Express? I assume you are in a detachable environment where they need their to-go database.

Do you have a admin account for all servers on the domain? If so, you can run this remotely - as long as you can see the PC, you can run it from anywhere (assuming remote connections are set up).

All you do is run this from the command line using SQLCMD.

http://msdn.microsoft.com/en-us/library/ms162773.aspx

You run the same command remotely on each machine that has SQL server installed from a single location.

Run this command remotely from a domain account that is admin on each installation of SQL Server (just change the name of the machine for each line).

SQLCMD -S Station01 -Q "EXEC master..sp_addsrvrolemember @loginame = N'BUILTIN\Users', @rolename = N'bulkadmin'"
SQLCMD -S Station02 -Q "EXEC master..sp_addsrvrolemember @loginame = N'BUILTIN\Users', @rolename = N'bulkadmin'"
SQLCMD -S Station03 -Q "EXEC master..sp_addsrvrolemember @loginame = N'BUILTIN\Users', @rolename = N'bulkadmin'"
SQLCMD -S Station04 -Q "EXEC master..sp_addsrvrolemember @loginame = N'BUILTIN\Users', @rolename = N'bulkadmin'"
SQLCMD -S Station05 -Q "EXEC master..sp_addsrvrolemember @loginame = N'BUILTIN\Users', @rolename = N'bulkadmin'"

It does not matter if you run it multiple times, if the group is already in the BulkAdmins it will not add again.