SQLCMD using Windows Authentication for non-admin users

For several years I have used a .bat file to launch several SQL Server processes.  It was originally created on SQL Server 2005, which was later upgraded to SQL Server 2008.  The server OS was Windows Server 2003 Standard SP2.  Last week we moved to a new server with Windows Server 2008 R2 Enterprise OS.  Now the .bat cannot be launched by non-admins.  Administrative users can launch the process by double clicking the .bat file, but when a non-admin user tries we get an error like "login failed for user WINDERHQ\dtuser".  Here is the content of the .bat file:

sqlcmd -S GP -d wndr -Q "truncate table wndr_inv_trxfr"
"C:\Program Files (x86)\Microsoft SQL Server\100\DTS\Binn\dtexec" /f "C:\SSISPackages\SSIS Packages\SSIS Packages\WinderInventoryTransfers.dtsx"
sqlcmd -S GP -d wndr -Q "EXECUTE udp_ValidationChecks"
sqlcmd -S GP -d wndr -Q "EXIT(SET NOCOUNT ON SELECT MSG FROM INV_TRXFR_MSG)"

If I use the -U and -P switches with an admin user id, of course, it works regardless of the status of the logged in user, but I don't want to do that.  I have created a proxy account and a credential.  The proxy user is a domain administrator and a SQL administrator.  I have also used the proxy account in place of the local account to run SQL server and SQL Server Agent.  The SQL Server instance is set up for mixed authentication.  What can I do to make this available for use by non-admins, without having to expose passwords with the -U and -P switches?
T HoecherlDeveloperAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Marten RuneSQL Expert/Infrastructure ArchitectCommented:
SQLCMD is to be executed by the windows user you have in mind. With the -E Switch
Link: http://technet.microsoft.com/en-us/library/ms162773.aspx

Regards Marten
0
Marten RuneSQL Expert/Infrastructure ArchitectCommented:
Oh, a bit fast there.
The users dont have rights on the server, thats your problem.
You need to add them, and I would create a role that has the rights you want them to have, i e truncate a table, launch the SSIS (what it does, unless it has its own SQL user imbedded), execute permission on udp_ValidationChecks and select permission on INV_TRXFR_MSG
Then assign this role to the users.

If there are many, put them in a AD OU Group, and create a user for this AD Group, and make it a member of the above explained database role.

Regards Marten
0
T HoecherlDeveloperAuthor Commented:
Actually, the -E switch is the default and doesn't need to be specified, although I did add it just to see and it made no difference.

I think I have done what you suggest with regard to permissions.  Let me tell you what I have done and you can see if it is what you mean.  I have not put them into a group, but I have taken one of the users - the one I am using to test - and at the database level I have given the user Alter and Execute permissions on stored procedures.  Then,  I gave Insert, Select, Update, Delete permissions on all of the tables that are part of the process and, for good measure, I gave Alter and Execute permissions on every stored procedure that is executed as part of this process.

The SSIS portion is working because I did imbed the credentials for a SQL admin in the SSIS package.  It is the other SQL functions that are failing.  Will creating a group and adding the users to it do anything more than this, other than the convenience of only having to set the permissions once?

T
0
Newly released Acronis True Image 2019

In announcing the release of the 15th Anniversary Edition of Acronis True Image 2019, the company revealed that its artificial intelligence-based anti-ransomware technology – stopped more than 200,000 ransomware attacks on 150,000 customers last year.

Marten RuneSQL Expert/Infrastructure ArchitectCommented:
Sounds right.

What I was proposing was a role in the database that you gave these rights to. And then add the user to the server+databaser and give them this role.

You can do it as per user ofcourse. It's just as good, maybe the roles are clearer and more manageble. But there is no difference for the end users.

Bye the way. they wont need the alter permission. If you grant the execute permission on a stored procedure, ALL TABLES INVOLVED will be granted the rights this stored procedure has.
The end user does NOT need more than the execute right to that stored procedure.

This applies as long as youre stored proc is within the SAME DATABASE

Regards Marten
0
T HoecherlDeveloperAuthor Commented:
OK thanks, Marten.  Any  other ideas, though, because I still get the error for non-admins.
0
Marten RuneSQL Expert/Infrastructure ArchitectCommented:
--in sql Query window (SSMS)
-- Run the lines until next GO to test the various parts
use master
go
execute as login = 'domain\user'
go
use yourdb
go
--execute your things, to see what error you'll get then.
-- for example
truncate table wndr_inv_trxfr
go
EXECUTE udp_ValidationChecks
go
EXIT(SET NOCOUNT ON SELECT MSG FROM INV_TRXFR_MSG)¨
-- the row above looks suspicous to me, Thing there is a error in the code.
go
--When done and error corrected do:
use master
go
revert
-- revert puts back to your login
go

Regards Marten
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Windows Server 2008

From novice to tech pro — start learning today.