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
permissions among users, SQL has provided access management using windows
the steps to create any local windows group
1. Search “Computer
Management” and open it.
“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
hackers from manipulate our database, we should always provide minimal access
It can be
done at object level and database level.
Advisable to provide access to only
required objects so that other objects can’t be accessed in case of any hacking
Keep all the interactions through
stored procedure and user should only be allowed to execute SPs (will not have
even select rights).
धन्यवाद मित्रो !!
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.
As we are installing a database on client PC then they
might be able to clone our system by attaching MDF files to another
(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.
Difference between foreign key and primary key:
In this post
I will share key differences between foreign key and primary key.
KEY uniquely identifies each record in a database table; main goal is to
prevent duplicate values for columns and provides unique identifier to each
is a column or field in current table and primary key of another table, the
foreign key points to another table using primary key of that table.
table shows all possible differences between MS-SQL Server table primary key and
We can have only one Primary key in a table
We can have more than one foreign
key in a table.
Primary Key can't accept null values.
Foreign key can accept multiple
By default, Primary key is clustered index.
You can manually create an index
(non-clustered) on foreign key.
Primary key supports Auto
Unique Constraint doesn't support
Auto Increment value.
The PRIMARY KEY uniquely
identifies each record in a database table
Foreign key is a field in the
table that is primary key in another table.
धन्यवाद मित्रानो, आपला प्रत्येक दिवस आनंदी आणि सुखी जावो