SQLserverSecurity

Topics: Microsoft SQL Server, SQL Server Management Studio, Microsoft Pages: 5 (393 words) Published: February 2, 2015


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
Department Manager: Sales
TSQL Add user: CREATE LOGIN [software\Sales]

    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 [Sales] FOR LOGIN [software\Sales];
GO

TSQL Grant permissions Read:
Grant Read on Parts_Movement TO Sales;
GO

References
Microsoft Developer Network. (2014). Retrieved from http://msdn.microsoft.com/en-
us/library/ms365303(v=sql.110).aspx

References: Microsoft Developer Network. (2014). Retrieved from http://msdn.microsoft.com/en-
us/library/ms365303(v=sql.110).aspx
Continue Reading

Please join StudyMode to read the full document

Become a StudyMode Member

Sign Up - It's Free