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
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.