Account Creation and management in SQL server 2005/2008
Using SQL server management studio
Demo names
Database: technoencyclopedia, techno
Login id: test
Password: test
Table: techno
Introduction:
Securing a database has always been a center of focus since the technology has started to build the database systems. However, managing the user account and keeping the data secure has become much easier as the time passes.
SQL server 2005 and 2008 provide a very friendly GUI to create, view and monitor different user accounts that can be given different permissions to access different objects of SQL server and the database.
In this article we will be discussing briefly about:
- Basic of security in SQL server.
- Different permissions that can be assigned to a user account.
- How can we create a user account?
- Account monitoring.
- Is there any difference in account security between SQL server 2005 and 2008.
Logins:
Now then, let’s start talking about SQL server security, the most popular word that we hear when we talk about security is “login”.
Logins are the instances with different login IDs and passwords which are given to each user who accesses the database.
Each database engine can have multiple logins and all of them can have different access rights. The logins are very similar to the one we see in Windows accounts.
Login IDs and passwords are stored in the master database of the database engine. Each login possesses different rights and access at server and database level.
All these access and permissions are assigned to the user through an admin account, which is generally owned by a database administrator.
There are two types of logins in SQL Server:
- Windows authentication
- SQL server logins
Windows authentication is unsecure in a way because the same login and password is used by the SQL that is used by Windows, which means a person who can login to Windows can also access the database.
Whereas SQL login is more secure and it can restrict users from performing different tasks.
Users:
The database users are one of the main entities of the database scope. These are defined at database level and are links between server login and database.
Database users can access different objects of a database like tables, stored procedures etc. depending on the privileges provided to them.
The link between users and logins is broken when you move or copy a database from one server to another.
DBO User:
The term is used for a user who is also known as a database owner. This user cannot be deleted from the database.
The guest user:
The guest user is a default user which is already created in a database. This is normally used by users who don’t have a user account to access the database, and possess very limited access rights.
Security enhancements that were made in SQL server 2005 are carried forward to SQL server 2008 and there are no changes made in the schema logics and security options.
Securable:
This is a new concept that was introduced in SQL server 2005 and is carried forward in SQL server2008 without any modifications. According to this concept, securable is anything that can be assigned different accessing permissions.
Roles in SQL server:
In both the versions of SQL 2005 and 2008, there are two types of roles, namely server roles and database roles. The server roles are by default, defined in SQL server. It allows user to create, delete and alter any other user. You cannot add any other server roles, you can only use the predefined ones.
The second type, which is database roles, assign the rights and privilege access to the users. You can create your own database role on objects within the database.
Following are the lists of predefined server and database roles:
SQL server roles:
BULK ADMIN: assigns/declines insert statement execution in bulk.
DB CREATOR: can create, change, and delete a database.
DISK ADMIN: can manage files on the disk.
PROCESS ADMIN: manages SQL server processes.
SECURITY ADMIN: manages logins, can read logs, create database permissions, etc.
SERVER ADMIN: possesses every option across the server, can also shut down the server.
SETUP ADMIN: manages other servers that are linked.
SYS ADMIN: have accessibility to perform anything on the server.
SQL SERVER DATABASE ROLES:
db_accessadmin: can create/delete user IDs.
db_backupoperator: can take database backups.
db_datareader: can view and select data from any table of the database.
db_datawriter: can insert and modify data in any table of a database.
db_ddladmin: possesses data definition language statements; admin rights.
db_denydatareader: cannot view or select data from tables.
db_denydatawriter: cannot insert or modify data in the tables.
db_owner: possesses all the permissions on a database.
db_securityadmin: manages all permissions, roles, and users.
Creating a user account:
Now let’s see how we can create an account using SQL Server Management studio.
- Open Object Explorer, drill down the security folder and right-click the login folder and select new login. A new window will appear as follows:
- Here, specify the login name that you want to be used as login ID. I’ve used test here.
- Click on the SQL server authentication and write the password and confirm it.
- Then you can see different options; enforce password policy is used to enable the password, Enforce password expirations enable the feature of password expiration, and the third option will ask the user to change the password every time he logs in.
Now, then below these options you can see a database drop-down. From here, you can select the database for which you are creating this user.
In the left upper corner you can see Select a Page menu. Now, select Server Roles from it as shown below:
You can see here the different server roles as we discussed above. I have selected bulkadmin for this particular user.
Now select User Mapping from Select a Page menu and the following window will appear:
Here you can see a list of databases. Select the database which you want to be enabled for this user and click on the browse button at the corner.
Another small window will appear. Click on the browse button.
Now, you can see another pop-up window here. This window contains the database role objects that we have talked about earlier; I’ve selected the db_owner for this user. Click Ok to go back.
Now you can see the database roles below the database list. Select the desired database role that you want to assign to this user and select the Securable.
Click the Add button to continue.
Here you can select the objects in which you want to create restrictions. Click Ok to go back and select Status from Select a Page menu.
Here, you can set status that if a user provides correct password, whether you will to grant him the permission to access the server or not. Enable or disable this login account. Click Ok to continue.
Now you can see your test account created under the login folder.
Let’s test this login ID by connecting on it.
Click on the Connect button and select Database Engine.
Type the login IDand password as follows:
Press the Connect button
A new instance is created in which you can view the database and the tables in the database that we have assigned to this user.
Now let’s see what happens if we try to access any other database
You can see that an error occurred when we tried to access database, techno, because we didn’t allow this user to access any other database, except technoencyclopedia.
You can monitor the access rights of this user in the following window:
Double-click the user and you can also change its rights.
CONCLUSION:
In the above article, we discussed about the security process in SQL 2005 and 2008. We also discussed the server and database roles plus how to create and monitor a user account. This is a major function that a DBA has to perform and must be aware of.
