no permission createobject

Tarek Halloun 36 Reputation points
2022-05-03T08:16:39.793+00:00

hello everyone

I have a script on sql that uses a vbscript to create an excel file

It was working well on a server 2008R2 and sql 2008r2 with excel 2007 but now we upgraded to server 2019 and sql 2019 with excel 2016 but the script broke

If i run the script in CMD , it works well

If i run if from sql i get no permission create object , meaning i cannot create the excel

I tried with dcom but nothing worked ( added everyone to the excel application )
i checked the logon users shown here and added the sql agent and server to the folder permissions but still same error198481-sql1.png198368-sql2.png198369-sql3.png

what can i try ?

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,711 questions

5 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Olaf Helper 40,816 Reputation points
    2022-05-03T08:39:16.377+00:00

    In current SQL Server version xp_cmdshell is disabled, for safety reasons. You have to enable it first.
    Using xp_cmdshell together with VB script is really not a good idea.

    See https://learn.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/xp-cmdshell-transact-sql?view=sql-server-ver15

  2. Tom Phillips 17,716 Reputation points
    2022-05-03T14:36:08.407+00:00

    Check the effective rights on the "ExcelFormatting" directory for MSSQLSERVER.

    Does your script run, if you run it manually from the SQL Server, without SSMS?

    That error can also mean VB cannot find a library used by the script.

  3. Erland Sommarskog 101K Reputation points MVP
    2022-05-03T21:57:01.977+00:00

    Maybe that is a good thing. Is the VBscript using the COM objects for Excel? Excel can open windows. You cannot open windows from a service, since it does not have a desktop. You may been using this for many years, but to me, this sounds like an accident waiting to happen.

    So I think you should reconsider this design altogether.

    0 comments
  4. YufeiShao-msft 7,056 Reputation points
    2022-05-04T06:57:37.167+00:00

    HI @Tarek Halloun ,

    What the user do you use to log in SSMS?
    This could also be a issue with Windows permissions. You might consider switching your file to other location, Windows does not like your file in plain C:\ dierctly. Make sure your antivirus or a company security policy are not organized.

    -------------

    If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    0 comments
  5. Tarek Halloun 36 Reputation points
    2022-05-06T07:40:23.923+00:00

    i ended up giving permission to the office16 folder and the DCOM excel app for the mssql account
    worked like a charm

    0 comments