GRANT statements issue Sch-M locks....Why?

We are experiencing blocking situations with a application that issues GRANT
statements. It becomes blocked trying to take out a Sch-M lock while other
long running report processes are holding Sch-S locks.
I'm wondering:
1) Why is the non-DDL GRANT statement wanting to lock the schema?
2) Is there any way to prevent it, or to prevent the reporting processes
from taking out Sch-S locks?
Thanks!1) Because GRANT _is_ DDL.
2) You can run the reporting processes WITH(NOLOCK), but that might cause
them to fail occasionally.
Jacco Schalkwijk
SQL Server MVP
"Fred" <Fred@.discussions.microsoft.com> wrote in message
news:C9D893C6-08C6-4749-AF8F-48E0A3ADDA05@.microsoft.com...
> We are experiencing blocking situations with a application that issues
> GRANT
> statements. It becomes blocked trying to take out a Sch-M lock while
> other
> long running report processes are holding Sch-S locks.
> I'm wondering:
> 1) Why is the non-DDL GRANT statement wanting to lock the schema?
> 2) Is there any way to prevent it, or to prevent the reporting processes
> from taking out Sch-S locks?
> Thanks!|||Hi
Isn't GRANT DCL ?
"Jacco Schalkwijk" wrote:

> 1) Because GRANT _is_ DDL.
> 2) You can run the reporting processes WITH(NOLOCK), but that might cause
> them to fail occasionally.
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "Fred" <Fred@.discussions.microsoft.com> wrote in message
> news:C9D893C6-08C6-4749-AF8F-48E0A3ADDA05@.microsoft.com...
>
>|||What if someone tries to drop the object while you are in the process of
GRANT'ing ?

> 1) Why is the non-DDL GRANT statement wanting to lock the schema?
Gopi
"Fred" <Fred@.discussions.microsoft.com> wrote in message
news:C9D893C6-08C6-4749-AF8F-48E0A3ADDA05@.microsoft.com...
> We are experiencing blocking situations with a application that issues
> GRANT
> statements. It becomes blocked trying to take out a Sch-M lock while
> other
> long running report processes are holding Sch-S locks.
> I'm wondering:
> 1) Why is the non-DDL GRANT statement wanting to lock the schema?
> 2) Is there any way to prevent it, or to prevent the reporting processes
> from taking out Sch-S locks?
> Thanks!|||Yes, good question. I could understand it needing to take a Sch-S to protec
t
the object while granting, but I don't see how it is a schema MODIFICATION t
o
grant permissions.
"gopi" wrote:

> What if someone tries to drop the object while you are in the process of
> GRANT'ing ?