Tuesday 2 May 2017

Access Deny from one Database to another Database

Step 1 : Use master database and create a new DB with any name
use master
create database p_test
use p_test
Step 2 : Go to Security -> Logins , can see all users that have created earlier.
Step 3 : Will create a new user with a given password
create login Test_Login with password='test', check_policy = off
User created successfully.
Step 4 : If we execute select statement to access another DB still we can.
Step 5 : Follow the below Query(changing ownership).
use p_test
go
sp_changedbowner 'Test_Login'
Step 5 : Use master DB and execute the below query to access deny another DB
use master
go
deny VIEW any DATABASE to Test_Login
use master
go
execute as login ='Test_Login'
go
Step 6 : select count(*) from mso.dbo.Items_Copy
Following error will get from output:

Msg 916, Level 14, State 1, Line 5
The server principal "Test_Login" is not able to access the database "MSO" under the current security context.

Notes : After creating the user permission you can access only three databases.
select * from sys.databases

Change created DB owner to distinct owner
use p_test 
GO
sp_changedbowner 'sa'
Drop Login and DB
drop login Test_Login
drop database p_test


No comments:

Post a Comment