SQL Server - Limit the databases view based on login

I have a situation where I want to limit the database view per user login.

For example: I have 3 databases in my SQL Server instance called MyDB, UserDB, RestrictedDB. I then create a 2 logins; User1 and User2. I then did a DENY VIEW ANY DATABASE TO [User1] and then DENY VIEW ANY DATABASE TO [User2]. I then made User1 the owner of UserDB by executing sp_changedbowner 'User1'

When I login as User1, I can only see UserDB which is exactly what I want. But of course when I login as User2, there is no database that he can view.

I made User2 as a db_owner as well by executing sp_addrolemember 'db_owner','User2' and logged in again but I still can't see UserDB. I did not attempt to execute sp_changedbowner on User2 as I think the result would be that User2 will be able to see UserDB but not User1.

What I need is for both 开发者_如何学PythonUser1 & User2 to be able to see just UserDB. How would I go about doing this?

Thanks in advance!

You can setup a security role granting privleges on tables, views, and their access all of that. Associate the user desired to the role you have created and you will get the desired result.

The following should create a role, login, user and provide SELECT only permission to the view in question. The same methods can be applied for whatever permissions you wish.

CREATE ROLE [db_views_role] AUTHORIZATION [dbo]
GO
GRANT SELECT ON [dbo].[vMyView] TO [db_views_role]
GO
CREATE LOGIN [my_login] WITH PASSWORD=N'PASSWORD', DEFAULT_DATABASE=[MyDatabase], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
CREATE USER [my_login] FOR LOGIN [my_login]
GO
EXEC sp_addrolemember N'db_views_role', N'my_login'
GO

The logins need to be associated with the databases by creating database users mapping to the logins.

In SQL Server, there are Server Logins and Database users. Try associated the Server Login to the specific database by:

Database > Security > Users > New User...

In Oracle, this is done via the 'GRANT' command. I don't know is SQL Server has GRANT

GRANT is used:

GRANT [permissions] ON TABLE to [user]

so

GRANT select, insert, update, delete ON TABLE to User2