SQLserverSecurity
SQL Server Security
Chris Sargent
University of Phoenix Online
DBM/502
July 14, 2014 There are a number of ways to secure MS SQL Server 2012. Security measures range from the physical location of the server, to operating system security measures, and finally permissions granted within MS SQL Server 2012. This paper will detail the steps to password protect MS SQL Server 2012 and how to use TSQL to add users with various permissions.
Password Protection
MS SQL server allows two types of logins Windows/operating system authenticated and SQL server authentication. This can be accomplished in either the configuration manager or through TSQL. In this case, the configuration manager will be used to create a user login. This will create a password of the database on a per user instance. Using Windows login will allow permissions based upon Windows groups with assigned privileges. The following steps are used:
1. Open the Microsoft SQL Server Management Studio
2. Right click on Security
3. Select New
4. Select Login
5. Change from Windows Authentication to SQL Server Authentication
6. Either enter or search for a specific user name
7. Enter password
8. Confirm password
9. At the bottom of the dialog box under default database select the database to password.
10. Click ok
User Matrix The below matrix is a listing of three users and what permissions the users would need to work with the database.
User
Read
Insert
Delete
Modify
Database Admin
X
X
X
X
Regular User
X
X
Sales Manager
X
Power User
X
X
X
Add a User With Permissions
Regular User JohnS
TSQL Add user: CREATE LOGIN [software\JohnS]
FROM WINDOWS (uses windows authentication for database engine access)
WITH DEFAULT_DATABASE = [Trucking];
GO
Granting a login to the specific database
USE [Trucking];
GO
CREATE USER [JohnS] FOR LOGIN [software\JohnS];
GO
TSQL Grant permissions Read and Insert:
Grant Read ON Parts_Movement TO JohnS;
GO
Grant Insert on Parts_Movement TO JohnS;
GO
Chris Sargent
University of Phoenix Online
DBM/502
July 14, 2014 There are a number of ways to secure MS SQL Server 2012. Security measures range from the physical location of the server, to operating system security measures, and finally permissions granted within MS SQL Server 2012. This paper will detail the steps to password protect MS SQL Server 2012 and how to use TSQL to add users with various permissions.
Password Protection
MS SQL server allows two types of logins Windows/operating system authenticated and SQL server authentication. This can be accomplished in either the configuration manager or through TSQL. In this case, the configuration manager will be used to create a user login. This will create a password of the database on a per user instance. Using Windows login will allow permissions based upon Windows groups with assigned privileges. The following steps are used:
1. Open the Microsoft SQL Server Management Studio
2. Right click on Security
3. Select New
4. Select Login
5. Change from Windows Authentication to SQL Server Authentication
6. Either enter or search for a specific user name
7. Enter password
8. Confirm password
9. At the bottom of the dialog box under default database select the database to password.
10. Click ok
User Matrix The below matrix is a listing of three users and what permissions the users would need to work with the database.
User
Read
Insert
Delete
Modify
Database Admin
X
X
X
X
Regular User
X
X
Sales Manager
X
Power User
X
X
X
Add a User With Permissions
Regular User JohnS
TSQL Add user: CREATE LOGIN [software\JohnS]
FROM WINDOWS (uses windows authentication for database engine access)
WITH DEFAULT_DATABASE = [Trucking];
GO
Granting a login to the specific database
USE [Trucking];
GO
CREATE USER [JohnS] FOR LOGIN [software\JohnS];
GO
TSQL Grant permissions Read and Insert:
Grant Read ON Parts_Movement TO JohnS;
GO
Grant Insert on Parts_Movement TO JohnS;
GO
References: Microsoft Developer Network. (2014). Retrieved from http://msdn.microsoft.com/en- us/library/ms365303(v=sql.110).aspx