Codechef4u is a community for computer professionals,by computer professionals,just like you; who loves sharing and helping each others,Join them
Share your post

SQL Server 2016 User Logins Management

This is my 2nd article on SQL server 2016, in this article I will explain in detail how to maintain user permissions or security.

SQL Server Security Management

A. Developers Access Control

It is very crucial to manage each user’s access rights as it can become hectic when need to manage 10-20 users with multiple permissions on their windows authentication.

To manage permissions among users, SQL has provided access management using windows groups.

Below are the steps to create any local windows group

1.  Search “Computer Management” and open it.
2.  Expand “Local Users and Groups” and then open Groups.

3.  Add new local windows user group and map windows users under it. Eg. “SQLUserGP_Read”, “SQLUserGP_Write” and “SQLUserGP_DBA”

Windows group can be added in same fashion like windows user.
It is always advisable to provide execute rights to all developers so execute below query for same.
GRANT EXECUTE TO [VSPC0129\SQLUserGP_Read]
GRANT EXECUTE TO [VSPC0129\SQLUserGP_Write]


B.  SQL/Web User Access Control

To restrict hackers from manipulate our database, we should always provide minimal access to users.
It can be done at object level and database level.

Object Level

Advisable to provide access to only required objects so that other objects can’t be accessed in case of any hacking attempts.

Database Level 

Keep all the interactions through stored procedure and user should only be allowed to execute SPs (will not have even select rights).

धन्यवाद मित्रो !! 

Thanks Friends 

How to prevent user to not copy SQL server data files

Prevent database file from copying

I am working in a product based company and will provide SQL Server express 2014 edition with our software.
I want your expert comment on database security. 

Problem

As we are installing a database on client PC then they might be able to clone our system by attaching MDF files to another server. 

(In my view, through database objects name and data, other companies can easily identify our workflow and can develop software easily)

So I want some type of encryption or file system solution to prevent user to take database files. And yes as we are using express edition, we can't use available SQL server options.

Rollback timeout issue with large data transactions?

I am using simple .net transaction (C# code using ado.net data reader and stored procedure) to delete some records I provided 30 seconds’ transaction timeout period.

But my stored procedure takes more than 30 seconds in large records case, I am using try – catch block to handle transaction.

In large data case when transaction rollbacks, rollback process again facing timeout because rollback process taking more than 30 seconds due to complex data table relations (multiple foreign keys constraints)

Doubts on situation,

1.  How to handle this new rollback timeout issue without increasing transaction main timeout (30 seconds), we need to wait till full rollback completes.
2.  In rollback timeout situation database is in complete blocked situation I am not able to execute SP_Who2 also.

These are two main issues, other minor issues are,

1.  In above situation, what happens to executed SP after timeout is it still in running state or paused.
2.  How we can minimize transaction rollback time?

 

Please provide your valuable solution with .net and SQL server