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
Wednesday, June 29, 2011
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
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
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
Subscribe to:
Posts (Atom)
