Monday, 26 October 2015
Friday, 23 October 2015
The SQL Server Alliance is becoming more popular in these days as a result various departments or client of the company would access same SQL Server Instance. When they worked in a combined instance, they do not want to make their database visible to other users. In this blog post we will discuss how to hide the database from users using Transact-SQL or using SQL Server Management Studio.
For this first we have to create two databases by using the following T-SQL query
Create database [Example_1]
Create database [Example_2]
Create logins for both newly created databases
create login [Example_1Login] with
create login [Example_2Login] with
Use the following T-SQL script to hide all users database from all logins. The database then only visible to sysadmin and database owners
revoke view any database to [Example_1Login]
Revoke view any database to [Example_2Login]
As we know that only sysadmin and owners of the database can see database. So run the below statement to select Example_1Login as an owner of Example_1 and Example_2Login as an owner of Example_2
Using SQL Server Management Studio
Right click on the root section (Server Name) of SQL Server Management Studio >>Click on Properties
Under select page column, select Permission >> Click on the account, and select deny to view the database
In the next step, right click on the newly created database >>Click on Properties
Select Files >>Change the owner to newly created account
Now Example_1Login can see only Example_1 database and Example_2Login can see only Example_2 database
There are limited options to hide your database, as we discussed above. Once your database login gets restricted with DENY VIEW to PUBLIC then only the owner of the database and sysadmin can see the database
Thursday, 22 October 2015
Cluster LogCluster log stores the entire information of the cluster that includes SQL nodes, storage details as well as management. Apart from these, it also contains information about statistics, startup, errors etc. So, it is clear that information contained in this file is more important and these log files are situated in the data directory of the Server.
While troubleshooting the problems occurred in the cluster, data in the cluster.log file is important. To know about the problems in cluster, it is better to check out the details by creating a cluster log file. Then, the query that arises will be how to create cluster.log file.
Steps To Generate Cluster Log FilesOne point to be noted while generating the cluster log file is that, the command for creating the log file is different since it depends on the operating systems. When you check out on different servers, you will get to know that, in Server 2003 the log files were generated automatically. However, from the later versions it has to be created separately. It is created with the help or support of cluster.exe command.
- From the nodes of cluster, open Command Prompt. Right click it and Run As Administrator.
- Type the command cluster log/g, where ‘g’ stands for ‘Generate’.
- cluster.log file will get generated and you can see it in C:\Windows\Cluster\Report
Let us see some other commands related to the generation of cluster.log files.
/Copy:< directory >
This command is used for copying the logs generated to single destination. This makes collection of logs much easier. Suppose, if you want to save the files at c:\archive\logs then, you need to type command as, c:\archive and then only you can execute the command ‘cluster log /g /copy:logs’.
/Span:< minutes >
It is to mention the minutes to go back for log collection. If you do not use this, you will get the history of several days. This helps to limit the contents of log and only includes the last few.
/Node:< node name >
It enables the stipulation of specific node. When this is done, logs for other nodes will not be created. If the option is not mentioned, all the nodes in cluster will have cluster.log file.
This is used to change the logging level and in Server 2008, the default level is 3. If the level is changed, more information will get logged, but there will be some effect on the performance of the system. If it is less than 3, less information will be present. Though in Server 2008, you can set the level from 0 to 10, the maximum is 5.
If you have come across with the situation to generate cluster.log files, hope this session will be helpful.
For more updates:
Subscribe for Blog posts [Click Here]
Follow FB Page [Click Here]
Follow FB Group [Click Here]
For Suggestion & Feedback mail us at email@example.com