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
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
drop database p_test
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_Logindrop database p_test
No comments:
Post a Comment