Security and User Administration of MSDE
This article will discuss the Security options and user administration that you may want to use on your instance of SQL Server or MSDE. The points covered here are relative to all versions and we look at how to manage these tasks using the Vale Software MSDE Manager.
SQL Server Security Model
To be able to access data from a database, a user must pass through two stages of authentication, one at the SQL Server level and the other at the database level. These two stages are implemented using Logins and User accounts respectively. A valid login is required to connect to SQL Server and a valid user account is required to access a database.
Login: A valid login name is required to connect to an SQL Server instance.
A login could be:
- A Windows NT/2000 login that has been granted access to SQL Server
- An SQL Server login, that is maintained within SQL Server
Logins are maintained within the master database. So, it is essential to backup the master database after adding new logins to SQL Server.
User: A valid user account within a database is required to access that database. User accounts are specific to a database. All permissions and ownership of objects in the database are controlled by the user account. SQL Server logins are associated with these user accounts. A login can have associated users in different databases, but only one user per database.
During a new connection request, SQL Server verifies the login name supplied, to make sure, that login is authorized to access SQL Server. This verification is called Authentication. SQL Server supports two authentication modes:
Windows authentication mode: With Windows authentication, you do not have to specify a login name and password, to connect to SQL Server. Instead, your access to SQL Server is controlled by your Windows NT/2000 account (or the group to which your account belongs to), that you used to login to the Windows operating system on the client computer/workstation. You must specify to SQL Server, all the Microsoft Windows NT/2000 accounts or groups that can connect to SQL Server
Mixed mode: Mixed mode allows users to connect using Windows authentication or SQL Server authentication. You must create a valid SQL Server login account and password. These are not related to your Microsoft Windows NT/2000 accounts. With this authentication mode, you must supply the SQL Server login and password when you connect to SQL Server. If you do not specify SQL Server login name and password, or request Windows Authentication, you will be authenticated using Windows Authentication.
It is important to note that whatever mode you configure your SQL Server to use, you can always login using Windows authentication.
Windows authentication is the recommended security mode. It is more secure and you don't have to send login names and passwords over the network.
Logins
You can use the MSDE Manager GUI interface to manage Logins
Open MSDE Manager and expand the <security> for the SQL Server Instance you want to create the login on.
Right Click on <Server Logins> and then select <Create New Server Login>
Enter the name for the login and the default database that this login will connect to. You also have to select the authentication as discussed earlier.
If you would like to grant this login the privileges of a specific role then this can be done the <Server Roles> tab.
If you select the database access tab and select one of the databases then a user is automatically created in that database with the same name as the login.
Users
Users can also be managed through MSDE Manager. They are managed through the user?s folder in each database. You are able to associate a login to the user you created
To create a new user in your database, expand your server and the database, Right Click on the <Users> folder and select <Create New User>
Fill in the <Name> and <Server Login Fields> and the user to any ?roles that may be appropriate? and click <OK>
The new user will appear in the user?s folder.
In summary the MSDE manager has excellent tools that allow you simply and easily manage your instances of SQL Server and MSDE.
Click here for Product Information on MSDE Manager
Click here for Product Information on Express Agent
View Screenshots >> Download free trial >> Buy online >>