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 names 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

These login names 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. A DBA must first 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. Your DBA must first create valid SQL Server login accounts and passwords. 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.

Point to note is that, whatever mode you configure your SQL Server to use, you can always login using Windows authentication.

Windows authentication is the recommended security mode, as it is more secure and you don't have to send login names and passwords over the network. You should avoid mixed mode, unless you have a non-Windows NT/2000 environment or when your SQL Server is installed on Windows 95/98 or for backward compatibility with your existing applications.

SQL Server's authentication mode can be changed using Enterprise Manager (Right click on the server name and click on Properties. Go to the Security tab).

Authentication mode can also be changed using SQL DMO object model.

Here is a list of helpful stored procedures for managing logins and users:

sp_addlogin	Creates a new login that allows users to connect to SQL Server using SQL Server authentication
sp_grantlogin	Allows a Windows NT/2000 user account or group to connect to SQL Server using Windows authentication
sp_droplogin	Drops an SQL Server login
sp_revokelogin	Drops a Windows NT/2000 login/group from SQL Server
sp_denylogin	Prevents a Windows NT/2000 login/group from connecting to SQL Server
sp_password	Adds or changes the password for an SQL Server login
sp_helplogins	Provides information about logins and their associated users in each database
sp_defaultdb	Changes the default database for a login
sp_grantdbaccess	Adds an associated user account in the current database for an SQL Server login or Windows NT/2000 login
sp_revokedbaccess	Drops a user account from the current database
sp_helpuser	Reports information about the Microsoft users and roles in the current database

Now let's talk about controlling access to objects within the database and managing permissions. Apart from managing permissions at the individual database user level, SQL Server 7.0/2000 implements permissions using roles. A role is nothing but a group to which individual logins/users can be added, so that the permissions can be applied to the group, instead of applying the permissions to all the individual logins/users. There are three types of roles in SQL Server 7.0/2000:

• Fixed server roles
• Fixed database roles
• Application roles

Fixed server roles: These are server-wide roles. Logins can be added to these roles to gain the associated administrative permissions of the role. Fixed server roles cannot be altered and new server roles cannot be created. Here are the fixed server roles and their associated permissions in SQL Server 2000:

Fixed server role	Description
sysadmin	Can perform any activity in SQL Server
serveradmin	Can set server-wide configuration options, shut down the server
setupadmin	Can manage linked servers and startup procedures
securityadmin	Can manage logins and CREATE DATABASE permissions, also read error logs and change passwords
processadmin	Can manage processes running in SQL Server
dbcreator	Can create, alter, and drop databases
diskadmin	Can manage disk files
bulkadmin	Can execute BULK INSERT statements

Fixed database roles: Each database has a set of fixed database roles, to which database users can be added. These fixed database roles are unique within the database. While the permissions of fixed database roles cannot be altered, new database roles can be created. Here are the fixed database roles and their associated permissions in SQL Server 2000:

Fixed database role	Description
db_owner	Has all permissions in the database
db_accessadmin	Can add or remove user IDs
db_securityadmin	Can manage all permissions, object ownerships, roles and role memberships
db_ddladmin	Can issue ALL DDL, but cannot issue GRANT, REVOKE, or DENY statements
db_backupoperator	Can issue DBCC, CHECKPOINT, and BACKUP statements
db_datareader	Can select all data from any user table in the database
db_datawriter	Can modify any data in any user table in the database
db_denydatareader	Cannot select any data from any user table in the database
db_denydatawriter	Cannot modify any data in any user table in the database

Create Login

USE [master]

GO

CREATE LOGIN [test5] WITH PASSWORD=N'Passw0rd', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF

GO

EXEC master..sp_addsrvrolemember @loginame = N'test5', @rolename = N'sysadmin'

GO

Creating User:-

CREATE USER [test5 ] FOR LOGIN [Test5]

    EXEC sp_addrolemember N'db_owner', N'test5'

END;

GO

SQL Server Gets PHP Support, Java Support on the Way

Microsoft today has announced new interoperability for SQL Server and Azure SQL Database through the delivery of a driver for PHP.

The driver, in preview, allows developers to use the PHP scripting language (version 5.5) to connect to SQL Server and Azure SQL Database. And, as part of the company's recent openness campaign, the full source code for the driver is also available.

This is just another step in Microsoft's interoperability movement. The company expects to release a JDBC Driver for SQL Server compatible with JDK7 in the coming weeks. Once available, the JDBC driver will allow developers to architect their applications with full connection capabilities to SQL Server against Java 7.

The preview driver is available here: Microsoft Drivers for PHP for SQL Server

The full source code is available on GITHUB here: Microsoft Drivers for PHP for SQL Server project

Suggestions, bug reports, and general feedback is being filtered through the Microsoft Connect site: SQL Server or SQL Database in Windows Azure

 courtesy by :-http://sqlmag.com/database-development/sql-server-gets-php-support-java-support-way.

The MidnightDBAs release Minion Reindex

The MidnightDBA team is announcing the release of a new, free index maintenance solution for SQL Server: Minion Reindex.

Database professionals need a reindex solution that is fully automated out of the box, intuitive to use, simple to customize, deeply configurable, and easy to report from. Existing index maintenance solutions fall short in ease of deployment, configurability, reporting, and more. The new Minion Reindex by MidnightDBA was designed by database administrators, for database administrators, to solve every one of these headaches.

Download Minion Reindex

Minion Reindex is a powerful and elegant tool that we are proud to have in our toolbox.  It makes our jobs easier, makes our clients’ maintenance operations more streamlined, and overall just makes the world a shinier, happier place to live in. 

Download Minion Reindex now for free at www.MidnightSQL.com/Minion.  And, join us at our Minion Reindex webinar on Monday October 27 at 12:00pm CT.  Check www.MidnightSQL.com/Minion for webinar information and links to documentation and feature tutorials.  

Features

We’re excited about a LOT of the features in Minion Reindex. Why is this particular solution worth your notice? Well…

·         Super easy install and removal, of course.

·         Easy MASS installation. One little Powershell script, and you can install Minion Reindex on hundreds of instances just as easily as on one.

·         Extreme granular configuration. Configure extensive settings at the default, database, and/or table levels with ease. And I mean every setting you can think of, from Fill Factor to sort in TempDB.

·         Order index operations by database and by table. This feature is very powerful; there are 2 levels of ordering at the database level, and another 2 levels for table ordering.

·         Flexible include and exclude – Reindex only the databases you want, using specific database names, and/or LIKE expressions. There’s even an advanced option to use regular expressions (regex) for excludes.

·         Live insight – Minion Reindex lets you see what’s happening while the routine is still running. See what Minion Reindex is doing every step of the way, and how much further it has to go.

·         Maximized maintenance window – Spend the whole maintenance window on index maintenance, not on gathering fragmentation stats. (Oh yeah, we let you schedule the frag stats gathering separately, if that’s what you want.)

·         Extensive, useful logging – Use the Minion Reindex log for estimating the end of the current reindexing run, troubleshooting, planning, and reporting.

·         Built in manual runs – Choose to only print reindex statements, and run them individually as needed.

·         Integrated help –Get help on any Minion Reindex object without leaving Management Studio.

SQL Server Update Statistics

Updating the statistics of all the tables regularly in your SQL Server Database is an integral part of Database Maintenance. This can be achieved in many ways. We can do this through a script or create a sql job scheduled to run at a certain time.

SQL Server also has a database option called "Auto Update Statistics" which by default is True. This feature follows a basic algorithm as to when the statistics of a table is to be updated . One drawback of this setting is if certain set of conditions are satisfied for a table then it starts updating the stats at that time. This could be in the middle of the day at peak hours and hence could create perforrmance issues in production , so it is sometimes safe to disable this and do the update statistics through a script or a sql job for a very large database. One of the easiest and fundamental steps involved in sql query optimization is updating the statistics of all tables involved in the query. Updated stats help the SQL query optimizer to generate optimized query plans for queries. The SQL Server maintains information like number of data pages used by each table, the total row count of each table and the number of DML operations which affected the keys since the last statistics upgrade. Besides this SQL Server also maintains index statistics To determine whether an index has it's stats updated we can run the following command: DBCC SHOW_STATISTICS (table_name , index_name) To Update the Stats on a table the following command can be run : UPDATE STATISTICS <table name> WITH FULLSCAN The above command will scan all the rows of the table to get the updated statistics.     UPDATE STATISTICS <table name> WITH SAMPLE 50 PERCENT    The above script can be run for very large tables in case we want to specify a percentage of the total no of rows. We should also update statistics for all tables in a database when we are upgrading from any version to a higher SQL Server version

The Page File

The page file is a disk file that the computer uses to increase the amount of physical storage for virtual memory. In other words, when the memory in use by all of the existing processes exceeds the amount of available RAM, the Windows operating system takes pages of one or more virtual address spaces and moves them to the page file that resides on physical disk. This frees up RAM for other uses. These “paged out” pages are stored in one or more page files that are located in the root of a disk partition. There can be one such page file on each partition.

                                    On a server running SQL Server, the objective is to try to keep SQL Server running using just the available physical memory. SQL Server itself goes to great lengths to ensure that it doesn’t over-allocate memory, and tries to remain within the limits of the physical memory available.

Given this basic objective of SQL Server, in most cases there is limited need for a page file. However, a frequently asked question is “Is there a recommended size for the page file?” The answer to this question, of course, is “it depends.” It depends on the amount of RAM installed and what virtual memory will be required above and beyond SQL Server. A general guideline is to configure a page file total of 1.5 to 2 times the amount of RAM installed in the server.

However, in large systems with a large amount of RAM (more than 128GB), this may not be

possible due to the lack of system drive space. Some good guidelines to adhere to in these cases are outlined in the following:

·         Configure an 8GB page file on the system drive.

·         Make sure that the Windows operating system startup parameters are configured to capture

A kernel dump in the event of a failure. Please see this article from Microsoft Support on how to configure this setting: http://support.microsoft.com/kb/307973.

·         Optional: Configure multiple page fi les (on disk volumes other than the system volume) that will be available for the OS to utilize if a larger page file is desired. It is recommended to use one additional page file of 200GB for each 256GB of RAM installed in the server.

In some cases, SQL Server and the OS might not cooperate well on sharing the available memory,

and you may start to see system warnings about low virtual memory. If this occurs, you will ideally add more RAM to the server, reconfigure SQL to use less memory, or increase the size of the page file. It may be better to reconfigure SQL Server to remain within the available physical memory than it is to increase the size of the page file. Reducing paging always results in better performance. If paging occurs, for best performance, the page file should be on fast disks that have minimal disk usage activity, and the disks should be periodically defragmented to ensure that the page file is contiguous on the disks, reducing the disk head movement and increasing performance. The metric in the Windows System Monitor to measure page file usage is Paging file: %Usage, which should be less than 70 percent.

How to Switch Between GUI and Core Mode in Windows Server 2012

Most of us might not be aware that there is a Windows Core version, which has only command-line without any GUI.

This core version relatively provides high level of security, as all GUI interface are removed, which also increases the performance of the system.

This might be helpful if you are running Windows server.

Up until Windows Server 2008, once you install the core version, or the full GUI version, you cannot switch back.

However in Windows Server 2012, it provides the following three modes, and you can easily switch between GUI to core, and core to GUI.

1.      Nomal GUI mode – The standard OS with full GUI features.

2.      Graphical mode – Management tools and Infrastructure. This mode provides only few essential GUI tools. For example, management tools like Server manger, Disk management console, etc.

3.      Core mode – No GUI in this mode. You’ll get only command prompt with powershell.

This tutorial explains how you can switch from Core version to GUI mode, and from GUI mode to Core version.

I. Switch from Core Version to GUI Mode

1. Launch Powershell

From the command prompt, launch the powershell as shown below.

C:\> powershell

PS C:\>

2. Import Server Modules

By default, to increase the server performance, all modules and commands are not loaded in server. We have to import the modules using the following command:

PS C:\> import-module serverManager

3. Change User Interface Mode

To change user interface from Command mode to GUI mode:

For full GUI mode:

Install-windowsfeature  Server-Gui-Mgmt-Infra, Server-Gui-Shell -Restart

For graphical management tools and infrastructure:

Install-Windowsfeature  Server-Gui-Mgmt-Infra -Restart

To change user interface from GUI mode to command mode:

For full command (core) mode:

Uninstall-Windowsfeature Server-Gui-Mgmt-Infra,  Server-Gui-Shell  -Restart

For graphical management tools and infrastructure:

Uninstall-Windowsfeature Server-Gui-Shell -Restart

For our example, let us do the following:

PS C:\> install-WindowsFeature Server-Gui-Mgmt-Infra, Server-Gui-Shell -Restart

After entering the above command, it will extract all binary files and start the installation. After completing installation server will reboot automatically. When the system starts, Windows will be running in the new mode.

II. Switch from GUI to Core Command Mode

1. Launch “Remove Roles and Features”

Open server manger -> Select “Manage” from the menu -> Select “Remove Roles and Features” as shown below.

Press next on the welcome page.

In the next screen, select the server from the server pool. By default our local server is selected, but we can perform add/remove feature task to remove server by adding server IP in server pool.

2. Select User Interface Features

Now, you can select or unselect the options under user interface and infrastructure features option as per your requirement.

To change user interface from GUI mode to Command mode:

For full command (core) mode: Uncheck both “Graphical Management and infrastructure” and “Server Graphical shell”.

For graphical management tools and infrastructure: Uncheck only “Server Graphical shell” option

3. Remove GUI Features

Select the features that you like to remove. This will also automatically select any dependent features that needs to be removed.

After the above step, the server will reboot. After the reboot, when the system comes back up, you’ll see not see GUI anymore. You’ll get only the command prompt with powershell.

Courtesy by: - http://www.thegeekstuff.com/2014/07/switch-windows-gui-mode/