The server principal " " is not able to access the database " " under the current security context. (Microsoft SQL Server, Error: 916)

The server principal " " is not able to access the database " " under the current security context. (Microsoft SQL Server, Error: 916)

This is the solution type of solution I found on the websites. Question is does it work, I dont't know because it didnt work in my condition so I really have no idea.

To resolve this issue:

1. After starting Management Studio 2008, select Databases in the Object Explorer once.

2. If the Object Explorer Details are not already showing, select View from the toolbar then select Object Explorer Details. 

3. Right click on any column header and make sure that “collation” is not checked. 

4. Refresh the database list by clicking the Refresh button located at the top of the Object Explorer Details panel.  You should have now have full access to your database at this point. 


Enjoy Have Bless day with MS SQL.

Could not Obtain Exclusive Lock On Database

Some time this error appears and First things comes in our mind is What has possibly gone wrong, I normally see this error when I am restoring the databases or overwrite a a dump database.

Since SQL is also a software and like every software it has its own way of operating and connecting with other entities. Let solve this issue, what steps will take care of this error.




1)  If the above error appears, close your Studio Management Console. Because sometimes its Studio Management that is holding a old connection. First and normally it fixes the problem.

2)  Second reason may come because of "Auto Close" property if turn "ON", Go in DB properties and turn off this property.

3) Check if any other window is open holding a connection to the specified DB. 


Use below script to turn "AUTO Close" off

SELECT DATABASEPROPERTYEX('Your DB' , 'IsAutoClose' ) AS [AutoClose]

GO

ALTER DATABASE [Your DB] SET AUTO_CLOSE OFF

GO

Some Daily Database Tips

Some Useful information for daily life DBA, Well I writing this post just to include things we as DBA need in everyday life you can find more information on other blogs and site this is something for information for new DBA guys. There are couple of commands that  can help us. Let looks us at commands. and Why we use them.

1)    sp_who

        Provides information about current users, sessions, and processes.

      sp_who [ [ @loginame = ] 'login' | session ID | 'ACTIVE' ]

          ACTIVE excludes sessions that are waiting for the next command from the user.

2)    sp_who2

Shows all the sessions that are currently established in the database.
sp_who2 also takes a optional parameter of a SPID. If a spid is passed, then the results of   sp_who2 only show the row or rows of the executing SPID.

3)    Kill

Terminates a user process that is based on the session ID or unit of work (UOW). If the specified session ID or UOW has a lot of work to undo, the KILL statement may take some time to complete.

4)    Sys

Sys is special system table as understandably by its name. What is purpose of Sys? Sys provide various functionalities like all table names in a database, schema level information , object and their information, etc. In short Sys is key player for for many system and database which you can find over here. Lets looks at some below.

5)    Sys.Table

Returns a row for each table object, currently only with sys.objects.type = U.

6)    Sys.Objects

Contains a row for each user-defined, schema-scoped object that is created within a database.

7)    Information_schema

Information schema is another SQL built in table, it has  schema level information.

An information schema view is one of several methods SQL Server provides for obtaining metadata. Information schema views provide an internal, system table-independent view of the SQL Server metadata. Information schema views enable applications to work correctly although significant changes have been made to the underlying system tables. The information schema views included in SQL Server comply with the ISO standard definition for the INFORMATION_SCHEMA.