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.
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';
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
user1should have access only toAAandBB- just create users foruser1only inAAandBBand that login will not be able to access any other databases on that server instance.