3

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
4

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.