×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

create sql view using batch file

create sql view using batch file

create sql view using batch file

(OP)
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

RE: create sql view using batch file

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...

Just quoting from there:

Quote (ITPro Today)

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/sqlpac...) 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

RE: create sql view using batch file

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

RE: create sql view using batch file

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:
www.gainfocus.biz/exceladdin.html

RE: create sql view using batch file

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

Use SQL commands to solve report problems and speed Crystal Reports

(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

RE: create sql view using batch file

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login


Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close