Granting only SELECT permissions on specific views to a specific user

Question

I have landed in a scenario were I have to let a user inside a database which is not his. Hence I want to make sure that he is only able to SELECT from the views that I deem he can read from.

I have googled and tested, and it seems like I have found a solution, but I want to make sure that I'm right.

So below should give me UserA with full rights to DatabaseA, and only SELECT permission to the 2 views in DatabaseB.

Is this correct, or am I opening up Pandora's box?

CREATE LOGIN UserA with password = 'UserA'

use DatabaseA
GO
create user UserA for login UserA
GO
EXEC sp_addrolemember N'db_owner', N'UserA'
GO

use DatabaseB
GO
create user UserA for login UserA
GO
grant select on DatabaseB.dbo.v_ViewForUserA1 to UserA
GO
grant select on DatabaseB.dbo.v_ViewForUserA2 to UserA
GO

Answer 1

Yes. What you have will do what you want. I do question the need for UserA to have db_owner access even on database A though. Do they have a need to be able to drop the database? Run backups that could interrupt your backup chain? Change recovery model (same problem)?

Generally even if a developer has complete control over a given database I would still limit them to db_datareader, db_datawriter, db_ddladmin and EXECUTE (database level). With very few exceptions that's sufficient for a development environment. Permissions of course get fewer the higher up the SDLC stack you go.