Wednesday, June 29, 2011

Hide Database details from the SQL Server Management Studio

Hide Database details from the SQL Server Management Studio

Hiding User Database in Object Explorer in SQL Server Management Studio

1.Create a user account (testuser)(make sure its not mapped to any Database)
2.Right Click on the upper section of the SQL (SQLSERVER Name)>Properties>Permissions>Click on the user account, and select Deny to view databases.
3.Right Click on the newly created DB, Properties,Files, and change the Owner to the newly created account.

Run the below query

USE TestDB

ALTER AUTHORIZATION ON DATABASE::TestDB to testuser


USE MASTER

DENY VIEW ANY DATABASE TO TestDB

Hiding System Objects in Object Explorer in SQL Server Management Studio

1.In SQL Server Management Studio, under Tools menu, click Options
2. In the Options dialog box, expand Environment and then select the General tab Select Hide system objects in Object Explorer and then click OK
3. In the Microsoft SQL Server Management Studio dialog box, click OK to acknowledge that the changes will come into effect once you restart SQL Server Management Studio
4.Close SQL Server Management Studio and reopen it again you will not see the System Objects in Object Explorer in SQL Server Management Studio

Tuesday, June 14, 2011

Indexed View Limitations

There are several requirements that you must take into consideration when using Indexed views.
1. View definition must always return the same results from the same underlying data.
2. Views cannot use non-deterministic functions.
3. The first index on a View must be a clustered, UNIQUE index.
4. If you use Group By, you must include the new COUNT_BIG(*) in the select list.
5. View definition cannot contain the following
a. TOP
b. Text, ntext or image columns
c. DISTINCT
d. MIN, MAX, COUNT, STDEV, VARIANCE, AVG
e. SUM on a nullable expression
f. A derived table
g. Rowset function
h. Another view
i. UNION
j. Subqueries, outer joins, self joins
k. Full-text predicates like CONTAIN or FREETEXT
l. COMPUTE or COMPUTE BY
m. Cannot include order by in view definition

Monday, June 13, 2011

Find all SQL Objects with QUOTED_IDENTIFIER set to OFF

--Find all SQL Objects with QUOTED_IDENTIFIER set to OFF

DECLARE @Type NVARCHAR(500),
@StrSql NVARCHAR(MAX)
SET @StrSql = ''

SET @Type = '''P'', ''TR'', ''V'', ''IF'', ''FN'', ''TF'''

SET @StrSql ='SELECT
SCHEMA_NAME(schema_id) AS [Schema], Name
FROM
sys.objects
WHERE
type IN (' + @Type + ') AND
OBJECTPROPERTY(object_id, ''ExecIsQuotedIdentOn'') = 0
ORDER BY SCHEMA_NAME(schema_id),Name'

EXEC sp_executesql @StrSql