Granting only SELECT permissions on specific views to a specific user 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
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.