Database

Creating a limited access MS-SQL user, especially in scenarios involving a DMZ server and corporate SQL server, requires careful consideration of security and access control. Here are some best practices and alternative approaches: Schema-Based Access: Instead of granting access at the database level, consider granting access at the schema level. Create a specific schema within DB_X and grant permissions only on that schema to WEBAPI_USER. This way, the user can see and interact with objects within that schema but won't have visibility into other schemas or databases. Stored Procedures: Limit interactions with the database through stored procedures. Grant execute permissions on specific stored procedures that the WEBAPI_USER needs to access, and ensure that these procedures encapsulate the necessary logic. This way, direct access to tables or other objects is not required. Row-Level Security (RLS): Depending on your SQL Server version, consider utilizing Row-Level Security (RLS) if applicable. RLS allows you to control access to rows in a table based on a user's identity or role. This can provide fine-grained control over data access. Database Ownership Chaining: While it's true that making WEBAPI_USER the owner of the database (DB_X) would grant visibility to all objects, it's generally not recommended for security reasons. Instead, consider enabling database ownership chaining selectively for specific scenarios where it makes sense. Custom Views: Create custom views that encapsulate the necessary logic and limit the columns and rows that WEBAPI_USER can access. Grant permissions on these views rather than directly on tables. Use Roles: Leverage database roles to group permissions logically. Grant the roles the necessary permissions and then add WEBAPI_USER to these roles. This helps in managing permissions in a more organized manner.

Nilesh M. Prajapati wrote: creates new database every month...Why database falls in recovery mode. My guess is that it would be due to how you 'create' it. Certainly if you start with a clean install of SQL Server (just the normal install databases) and if you run you app and it impacts that database, then yes it is specifically how you are creating it.

It sounds like you have a scenario where you want to save data across multiple tables in a database using a single form. Additionally, you want to create a master table to store foreign keys and display the data in the frontend. Let's break down your requirements: 1. Generating a Common ID for Multiple Tables: When you want to add data to multiple tables at once and have them share a common identifier, you typically use a primary key (ID) that is common across all related tables. This can be achieved through database design and relationships. For example, let's say you have four tables: TableA, TableB, TableC, and TableD. Each of these tables has its own data, but they all share a common identifier, which could be a foreign key linking to a MasterTable. Here's a simplified example: CREATE TABLE MasterTable ( MasterID INT PRIMARY KEY, -- Other columns as needed ); CREATE TABLE TableA ( ID INT PRIMARY KEY, MasterID INT, -- Other columns for TableA FOREIGN KEY (MasterID) REFERENCES MasterTable(MasterID) ); -- Repeat the same structure for TableB, TableC, and TableD When you insert data into MasterTable, you generate a unique MasterID and use it as a foreign key in the other tables. This way, you can maintain relationships between the tables. 2. Displaying Table Rows as Columns in the Frontend: If you want to display data from a table row as columns in the frontend, you'll need to use SQL queries or your backend programming language to transform the data before sending it to the frontend. For example, suppose you have a table named Data: CREATE TABLE Data ( ID INT PRIMARY KEY, MasterID INT, ColumnName VARCHAR(50), ColumnValue VARCHAR(50), FOREIGN KEY (MasterID) REFERENCES MasterTable(MasterID) ); This table stores data in a key-value pair format, where each row represents a piece of data related to a MasterID. To display this data with columns dynamically created based on the ColumnName values, you can use a pivot query. Here's a simplified example in SQL: SELECT MasterID, MAX(CASE WHEN ColumnName = 'Column1' THEN ColumnValue END) AS Column1, MAX(CASE WHEN ColumnName = 'Column2' THEN ColumnValue END) AS Column2, -- Add more columns as needed FROM Data GROUP BY MasterID; This query transforms rows into columns based on the unique values in the ColumnName column. Keep in mind that the specifics of these solutions might depend on the exact requirements of your application, the database system you're usin

An insert implies an index update; or a "split" somewhere. A simple append might be different. In effect, the "internal" state becomes dirty. Because it is "lite". "Before entering on an understanding, I have meditated for a long time, and have foreseen what might happen. It is not genius which reveals to me suddenly, secretly, what I have to say or to do in a circumstance unexpected by other people; it is reflection, it is meditation." - Napoleon I

Thanks for your response. What about this page? This says that there is: SQLite Syntax: upsert-clause[^] This is the INSERT statement, it clearly shows the upsert clause: SQLite Syntax: insert-stmt[^] The difficult we do right away... ...the impossible takes slightly longer.

Here is the link I posted to your QA question that was reported then deleted. Data Models[^] - lots of free, preprepared database schemas.

yes, that's true got good result by reconstructing database, eliminating unnecessary open connections, creating missing index, converting scan to seek, minimizing scan re-looping, distinct elimination / avoidant etc. thanks

Add a "row number"? [ROW_NUMBER (Transact-SQL) - SQL Server | Microsoft Learn](https://learn.microsoft.com/en-us/sql/t-sql/functions/row-number-transact-sql?view=sql-server-ver16#general-remarks) "Before entering on an understanding, I have meditated for a long time, and have foreseen what might happen. It is not genius which reveals to me suddenly, secretly, what I have to say or to do in a circumstance unexpected by other people; it is reflection, it is meditation." - Napoleon I

I am going to guess you have an application. Users (plural) use the application not the database. The application, not the users, use the database. So there is only one user which is only visible to the application. The reason for this is because attempting to manage users both at the database and application level rapidly becomes a problem and provides no benefit. The application itself, should provide a mechanism to validate each user. Then you can do things like log actions, in the application, for each user in the database (a table for that.) The application will use the database to implement this but it does not have anything to do with database users.

Brilliant! I guess I was overthinking it. Thanks, Richard. You saved the last few hairs I have left. The difficult we do right away... ...the impossible takes slightly longer.

It's a choice.

Whoops! I guess that's why nobody else recommended that. I forgot that ORDER BY is not allowed in derived tables. I think the temporary table is probably your best option. The difficult we do right away... ...the impossible takes slightly longer.

Check the "default project" list in the package manager console. If you don't specify the -project argument, this is the project that's used. EF Core tools reference (Package Manager Console) - EF Core | Microsoft Learn[^] "These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer