Category Archives: SQL Server

How to search error from SQL Server error log or SQL Server Agent Log?

1 Reply

How to search error from SQL Server error log or SQL Server Agent Log?

We can find the particular error or information from error log by passing below parameters to XP_ReadErrorLog extended procedure.


EXEC sys.xp_readerrorlog @p1,@p2,@p3,@p4

@P1 = Value of error log file you want to read: 0 = current, 1 = Archive #1, 2 = Archive #2, etc…

@P2 = Log file type: 1 or NULL = error log, 2 = SQL Agent log

@P3 = Search string 1: String one you want to search for

@P4 = Search string 2: String two you want to search for to further refine the results

Please note all the parameters are optional.

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