CODEWITHSUNDEEP
sql-serverlocking
Daryl Van Sittert
Daryl Van Sittert

Reputation: 877

Sql Server permission for read-only user to prevent locks

In a SQL Server database, there is a table that has millions of records, that gets insert to and updated frequently with new data.

Now the client has requested that they write their own reports off this table.

I am very concerned about them not writing reports correctly and that they lock the table on a long running reports.

Can I give them credentials that cannot lock a table

I.e. Can only do dirty reads similar to WITH (NOLOCK)?

Upvotes: 3

Views: 4441

Answers (1)

codingbadger
codingbadger

Reputation: 43984

I guess you could create a view over the table in question and add NOLOCK to that query. Then give their user account read only access to the view rather than the base table?

CREATE VIEW dbo.ReportingView
AS

SELECT COL1, COL2, COL3

FROM dbo.BASETABLE (NOLOCK)

Upvotes: 3

Related Questions