create sql view using batch file

[elly00]

Hi

is it possibile to delete and tha generate a sql view using a .bat file? That because I don't want to give the full access of the db to the customer

thanks
Elena

 

[Chris Miller]

Elena, that's a way too naive approach.

I think you need to dig deeper into the security system of MSSQL.
And learn about the architecture of MSSQL Server.

Heres just a google search result about that:

https://www.itprotoday.com/sql-server/protect-your-sql-objects-unwanted-changes

Just quoting from there:

Microsoft added several msdb roles that facilitate more security during SQL Server Integration Services (SSIS) package deployment and execution, such as db_ssisadmin, db_ssisltduser, and db_ssisoperator.

I'm not saying this article will give you all you need, but the first thing to extract from it is that changing a database should be made an SSIS package. And the other thing therefore to understand is what SQL Server integration services is and what roles there are in it, like the db_ssisadmin.

Here's another take on this, not specific to MSSQL only:

https://martinfowler.com/articles/evodb.html

It talks of tools like Flyway.

For MSSQL the currently best tools are with SQL Server Database Tools (SSDT) Visual Studio projects to create and maintain packages you can apply using SQLPackage (

https://learn.microsoft.com/en-us/sql/tools/sqlpackage/sqlpackage?view=sql-server-2017)

That still requires thinking about which account would execute this securely.

I think you have a lot of reading to do before you can decide what exactly to use and how, but surely not a bat file.

One of the simplest to implement solutions I could think of is using a setup (MSI, setup.exe) that encapsulates the task to make the db change at the smae time also applying the application change. Especially if you're using setups to deploy the application itself alredy anyway.

Chriss

 

[Mike Lewis]

Elena,

As a very rough guide, the SQL Server equivalent of a batch file is a T-SQL script. A script can contain almost any commands that are valid in the language, including CREATE VIEW, DROP VIEW, etc. But unlike a batch file, you don't run it at the operating system level, but rather within the context of SQL Server itself.

As for not wanting to give the users full access, that's a perfectly normal situation. SQL Server has an extensive system of roles and permissions to deal with that. The links that Chris posted will help you understand that.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[dgillz]

Is this related to your question in the Crystal Reports forum?

If so, there is no need to create a view to do this with Crystal. You simply give crystal the SELECT part of your SQL as a data source. This is called a SQL command in Crystal. And only the people who have access to the rpt file will be able to run the report.

Macola and SAP Business One Consultant
Check out our Macola tools:

http://www.gainfocus.biz/exceladdin.html

 

[Mike Lewis]

To follow up Dgillz's suggestion re using SQL commands in Crystal Reports, you might find my article useful:

http://www.hexcentral.com/articles/crystal-sql-commands.htm]Use SQL commands to solve report problems and speed Crystal Reports[/url]

(Although the article is over ten years old, the basic principles still apply.)

The point to remember is that the command normally returns a result set which you then use in a report. In the case of CREATE VIEW, etc., there is no result, but that shouldn't matter. Once you have sent the command and created your view, the view will be available in other parts of the report.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Mike Lewis]

That said, I'm not sure why you would want to create a view merely for the use of a report. Wouldn't it be easier just to use the underlying SELECT statement as your data source?

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads