Category: SQL Server

  • SQL Server Execution Context and Security

    What is Execution Context?

    Whenever User log on or connects to SQL Server, it will create the User Session. Whenever that user executes the statement SQL Server uses that session’s user id, permission, password to execute the query. That session is called execution context.

     

    Execution context is represented by a pair of security tokens: a login token and a user token. The tokens identify the primary and secondary principals against which permissions are checked and the source used to authenticate the token. A login connecting to an instance of SQL Server has one login token and one or more user tokens, depending on the number of databases to which the account has access.

     

    What is Principals in SQL Server 2005?

    Principals are the individuals, groups, and processes that can request SQL Server resources. Principals are categorized by their scope. Every principal has security identifier

     

    -> Windows level

    -> SQL Server level

    -> Database level 

    Windows-level principals

    • Windows Domain Login
    • Windows Local Login

    SQL Server–level principal

    • SQL Server Login

    Database-level principals

    • Database User
    • Database Role
    • Application Role

    What are SQL Server Securables?

    SQL Server Securables contains three scopes, which are used to assign permissions to users. The securables are nested and each securable contains various other securables. The securable scopes are as follows:

    ·         Server: It includes server roles, logins, etc.

    ·         Database: It includes database users, application roles, database roles, etc.

    ·         Schema: It includes various database objects such as tables, views, procedures, etc.

    The securables are used to assign permissions to the users based on scope and the tasks assigned. The issues related to the connectivity to databases, accessing database objects, etc., can be resolved by granting or denying the permissions to the users.

     

    What is the use of the Public database role in SQL Server?

    Every database user belongs to the public database role. When a user has not been granted or denied specific permissions on a securable, the user inherits the permissions granted to public on that securable. 

    Explain Certificate based SQL Server Logins/Principals?

    Server principals with names enclosed by double hash marks (##) are for internal system use only. The following principals are created from certificates when SQL Server is installed, and should not be deleted.

    • ##MS_SQLResourceSigningCertificate##
    • ##MS_SQLReplicationSigningCertificate##
    • ##MS_SQLAuthenticatorCertificate##
    • ##MS_AgentSigningCertificate##
    • ##MS_PolicyEventProcessingLogin##
    • ##MS_PolicySigningCertificate##
    • ##MS_PolicyTsqlExecutionLogin##

     

     

     

  • SQL Server 2005 Schema, Schema Properties, Schema T-SQL

    What is Schema in SQL Server 2005? Explain its properties with example?

    A schema is nothing more than a named, logical container in which you can create database objects. A new schema is created using the CREATE SCHEMA DDL statement.

    Properties

    • Ownership of schemas and schema-scoped securables is transferable.
    • Objects can be moved between schemas
    • A single schema can contain objects owned by multiple database users.
    • Multiple database users can share a single default schema.
    • Permissions on schemas and schema-contained securables can be managed with greater precision than in earlier releases.
    • A schema can be owned by any database principal. This includes roles and application roles.
    • A database user can be dropped without dropping objects in a corresponding schema.

     

     

    Create database SQL2k5

    Use SQL2k5

     

    — Created Schema Employee —

    Create Schema Employee

     

    — Created table in Employee schema —

    Create Table Employee.EmpInfo

    (

    EmpNo int Primary Key identity(1,1),

    EmpName varchar(20)

    )

     

    — data insertion —

     

    Insert Into Employee.Empinfo Values(‘Jshah-3’)

     

    — Data Selection —

    Select * From Employee.Empinfo

     

    — Created another schema HR —

    Create Schema HR

     

    — Transfer Objects between Schemas —

    ALTER SCHEMA HR

    TRANSFER Employee.Empinfo

     

    — Assigning Permission to Schema —

    GRANT SELECT ON SCHEMA::HR TO Jshah

     

  • Download SQL Sever 2005/SQL Server 2008 Samples

    You can download Microsoft SQL Server 2005/Microsoft SQL Server 2008 Samples.

    http://www.codeplex.com/MSFTDBProdSamples/Release/ProjectReleases.aspx?ReleaseId=4004

    OR

    http://www.codeplex.com/

  • Sqlcmd: Error: Microsoft SQL Native Client: An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections.

    1. Enable remote connections on the instance of SQL Server that you want to connect to from a remote computer.

              Use surface area configuration tool, to allow remove connections.

     

    1. Turn on the SQL Server Browser service.

    (Start -> Run -> Services.msc -> start the SQL Server browser services)

     

    1. Configure the firewall to allow network traffic that is related to SQL Server and to the SQL Server Browser service.

     

    For SQL Server Service (MSSQLServer)

    Start -> Settings -> Control Panel -> Network Connections -> Active Connections -> Properties – Advanced Tab -> Windows Firewall Settings – Add Port -> In the Name text box type SQL Server Instance Name -> in the PORT number text box type 1433 -> Select TCP/IP -> OK

     

    For SQL Browser Service

    Start -> Settings -> Control Panel -> Network Connections -> Active Connections -> Properties – Advanced Tab -> Windows Firewall Settings – Add Port -> In the Name text box type SQL Server Browser Service -> in the PORT number text box type 1434 -> Select UDP -> OK

     

  • How do I install the Service Pack?

     

    Follow the below steps before installing service packs on production server.

    Step 1: Get all the information about the latest service packs and their reviews

     

    Step 2: Install Service Pack on test server

     

    Step 3: Test the application functionality with the SQL Server with new service pack

     

    Step 4: If application works properly with the Test database server, take the sign off from business team and development team.

     

    Step 5: Before applying service pack on production server, please do the below steps

    Cold Backup

    Take a full server backup from the Operating System level with the SQL Server services turned off.

    Make a note of startup parameter, SQL Server Services Users/Network rights, memory allocation etc.

    This will guarantee you can return to a previous state if something goes wrong during the upgrade

     

    Hot Backup

    Turn on the SQL Server Services and take the backup of all system databases (Master, Model, MSDB and Copy the Resource database file) and user databases

     

    Step 6: Plan the downtime of the production server for the Service Pack installation and inform the users for the same.

     

    Step 7: Create the backup and restore plan for “What to do if the application is not working properly after service pack installation?”

     

    Step 8: Download the latest service pack and install it using “Setup.exe”

     

    Step 9: Again test the production application and production database server are working properly or not.