CODEWITHSUNDEEP
sql-serverdatabase
Kyoungmun Chang
Kyoungmun Chang

Reputation: 1

How to block access to databases except those permitted to access SQL Server

In SQL Server, there is a user user1 who can access tables in databases AA and BB.

Then I want user1 not to access all other databases except AA, BB.

That is, user1 should not access any tables in any other databases except AA and BB.

Upvotes: 0

Views: 527

Answers (2)

Wahm
Wahm

Reputation: 13

Take a look at this

SQL Server : can you limit access to only one table

exec sp_msforeachtable "DENY SELECT ON '?' TO [username];"
GO

GRANT SELECT ON [schemaName].[tableName] to [username]
Go

Upvotes: 0

TheGameiswar
TheGameiswar

Reputation: 28940

You don't need to explicitly deny permissions ,if they don't have access,you can ignore 

--create role in database
    create role test1

    --grant access on table t to test1
    GRANT SELECT ON [dbo].[T]  TO [test1]
    GO

    --you can even define column level permissions ,now users assigned to role test1 ---can view only column A of table T
    GRANT SELECT ON [dbo].[T] ([A]) TO [test1]

    --finally assign user john to role

    EXEC sp_addrolemember 'test1', 'john';

Upvotes: 1

Related Questions