Hide all databases from users / login in SQL Server

Hide all databases from users / login in SQL Server

Steps

1. Create SQL login with some name kalyan_login, here dont specify any user mapping

2. From SSMS

USE master;

GO

DENY VIEW ANY DATABASE TO kalyan_login;

3. Granting database access to kalyan_login

USE master;

GO

ALTER AUTHORIZATION ON DATABASE::kalyandb TO kalyan_login;

GO

 

Now the login is able to view only one database that he has access

You can use the below command to hide databases from all logins

DENY VIEW ANY DATABASE TO PUBLIC;

Identity Column value jumps to 1001 in SQL Server 2012

Recently we upgraded database from SQL Server 2008 to SQL Server 2012 version, We identified an issue with Auto increment identity columns.

If we restart SQL Server 2012 instance Identity column (auto-increment) value suddenly jumps to 1001 if the datatype is int and jumps to 10000 if the datatype is bigint.

Workaround:
Dont use auto-increment identity columns and use sequence feature in SQL Server 2012.