Category Archives: SQL Server

How to Alter a SQL Server Database as Single User Mode and as Multi User Mode

Leave a reply

You can use the SQL Server Enterprise Manager for SQL Server 2000 to set a database to a single user mode or to multi user mode. Similarly, SQL Server Management Studio can be used for SQL Server 2005 for changing a database to single user mode or to multi user mode. Also you can alter the database access mode by using sql commands like ALTER DATABASE and sp_dboption.

 

ALTER DATABASE [MyDBName] SET MULTI_USER WITH NO_WAIT

ALTER DATABASE [MyDBName] SET SINGLE_USER WITH NO_WAIT

or

EXEC sp_dboption ‘MyDBName’, ‘single user’, ‘false’

EXEC sp_dboption ‘MyDBName’, ‘single user’, ‘true’

Find the SQL Server Service Startup Time

Leave a reply

Find the SQL Server Service Startup Time

 

Many times for collecting or analyzing performance counter from the different DMV we need to collect the data of many days, so at that it is necessary to find the SQL Server Service startup time. We can find it as below.

 

1)       Every time when SQL Server restarts, It creates the “TempDB” so from the TempDB creation time you can find SQL Server startup time.

SELECT CREAT_DATE FROM SYS.DATABASES WHERE NAME = ‘TEMPDB’

2)       If you error log is not flushed you can use the XP_ReadErrorlog and from there you can find the SQL Server startup time

3)       We can also find it using sys.dm_exec_requests  DMV for startup time

4)       We can also find it from Master..SysProcesses system table by analyzing the login time for the System Processes (for i.e. LAZYWRITER)

Execute As in SQL Server 2005

Leave a reply

When an EXECUTE AS statement is run, the execution context of the session is switched to the specified login or user name. This will help database administrator to check the different user permissions. `Execute As` is also very helpful when a user wants to execute a stored procedure in the context of another user.

Syntax
Execute as user = ‘SQL_JShah’
select * from CompanyProducts.Products

SQL Server Execution Context and Security

3 Replies

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

6 Replies

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