0

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.

James Z
  • 12,209
  • 10
  • 24
  • 44
Kyoungmun Chang
  • 1
  • 1
  • 1
    Google something like "SQL server user mapping". It is too broad question to be answered in the QA format of StackOverflow. – Andrey Korneyev Feb 11 '16 at 11:57
  • Well, you have a **login** at the server level - and then you can define a **user** for that login in each database where that login should have access to. So if `user1` should have access only to `AA` and `BB` - just create users for `user1` only in `AA` and `BB` and that login will not be able to access any other databases on that server instance. – marc_s Feb 11 '16 at 13:03

2 Answers2

1

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';
TheGameiswar
  • 27,855
  • 8
  • 56
  • 94
0

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
Community
  • 1
  • 1
Wahm
  • 13
  • 1
  • 1
  • 8