| Answer / Solution |
 |
HELP FILE: DataPlus SQL Security
- db_owner of DataPlus database
- db_owner of application database
- db_owner of DataPlus database
- db_datareader of application database
SQL Server Security
Server
- Option 1: Typical security for DataPlus is Windows authentication
- Option 2: sa User
- Option 3: custom User with database rights
|
Authentication in SQL Server
SQL Server supports two authentication modes, Windows authentication mode and mixed mode.
- Windows authentication is the default, and is often referred to as integrated security because this SQL Server security model is tightly integrated with Windows. Specific Windows user and group accounts are trusted to log in to SQL Server. Windows users who have already been authenticated do not have to present additional credentials.
- Mixed mode supports authentication both by Windows and by SQL Server. User name and password pairs are maintained within SQL Server.
Security Note:
Microsoft recommends using Windows authentication wherever possible. Windows authentication uses a series of encrypted messages to authenticate users in SQL Server. When SQL Server logins are used, SQL Server login names and passwords are passed across the network, which makes them less secure. Note:
Logins are distinct from database users. You must map logins or Windows groups to database users or roles in a separate operation. You then grant permissions to users or roles to access database objects. |
Login Types
Server
- Option 1: Typical security for DataPlus client is Windows authentication
- Option 2: Windows Group, users are members of the group
- Option 3: Windows user login
|
SQL Server supports three types of logins:
- A local Windows user account or trusted domain account. SQL Server relies on Windows to authenticate the Windows user accounts.
- Windows group. Granting access to a Windows group grants access to all Windows user logins that are members of the group.
- SQL Server login. SQL Server stores both the username and a hash of the password in the master database, by using internal authentication methods to verify login attempts.
|
Fixed Server Roles
|
Fixed server roles have a fixed set of permissions and server-wide scope. They are intended for use in administering SQL Server and the permissions assigned to them cannot be changed. Logins can be assigned to fixed server roles without having a user account in a database.
Security Note:
The sysadmin fixed server role encompasses all other roles and has unlimited scope. Do not add principals to this role unless they are highly trusted. sysadmin role members have irrevocable administrative privileges on all server databases and resources. |
Fixed Database Roles
|
Fixed database roles have a pre-defined set of permissions that are designed to allow you to easily manage groups of permissions. Members of the db_owner role can perform all configuration and maintenance activities on the database.
For more information about SQL Server predefined roles, see the following resources. |
Database Roles and Users
|
Logins must be mapped to database user accounts in order to work with database objects. Database users can then be added to database roles, inheriting any permission sets associated with those roles. All permissions can be granted.
You must also consider the public role, the dbo user account, and the guest account when you design security for your application.
The public Role
The public role is contained in every database, which includes system databases. It cannot be dropped and you cannot add or remove users from it. Permissions granted to the public role are inherited by all other users and roles because they belong to the public role by default. Grant public only the permissions you want all users to have.
The dbo User Account
The dbo, or database owner, is a user account that has implied permissions to perform all activities in the database. Members of the sysadmin fixed server role are automatically mapped to dbo. |
Information Source |
FAQ ID # 49
Print this FAQ