Controlling visibility of databases and server level setting by Contained Database
Introduction:
Contained Database is a new feature of SQL Server 2012. A contained database is a database that is isolated from other databases and from the instance of SQL Server that hosts the database. SQL Server supports contained database users for both Windows and SQL Server authentication.
Issue:
- Unauthorized logins can see list of databases and names.
- Unauthorized logins can see few instance level settings and details.
- During the migration need to migrate logins and users
- Dual work creating login and user
Steps to created contained database and user
- Change the setting at instance level.
Right click on instance → Properties→ Advanced Enable Contained Databases true
Else we can run the command to change instance level setting.
Command: sp_configure ‘contained database authentication’,1
GO
RECONFIGURE
GO
- Create new database and with containment type ‘Partial’
3.Create user on database
Expand the contained database à security user
New user
Right click Users → new user (user id and password)
Provide permissions whatever needed
How to connect contained database.
- Click connect and enter username and password.
- Please mention contained database name at the connect to database
- Once connected that person can see only that particular database (contained database which that login has permission only)
Conclusion:
We can control of visibility of all databases names which are available in instance, isolated database connections, server level settings and during migration not required to move logins separately
