Grant permission (Sch-M lock)

When I tried to grant a Select permission on a table to a
user, it was blocked by any users who were currently
reading that table.
The grant command in QA issued lock Sch-M (Schema
modification) lock. Why is it issued Sch-M lock ?
Is it mean I cannot grant permission only any table while
users access it ?Hi Johnny,
Changes to schema information can not occur while the object is in use. I
suggest you continue to try of schedule it over night.
As an alternative to using SQL users ID you could use Windows groups to
secure your tables or use Database roles and add your users to the role
I hope this helps
regards
Greg O MCSD
http://www.ag-software.com/ags_scribe_index.asp. SQL Scribe Documentation
Builder, the quickest way to document your database
http://www.ag-software.com/ags_SSEPE_index.asp. AGS SQL Server Extended
Property Extended properties manager for SQL 2000
http://www.ag-software.com/IconExtractionProgram.asp. Free icon extraction
program
http://www.ag-software.com. Free programming tools
"Johnny" <jtao@.ssc.nsw.gov.au> wrote in message
news:058701c3b569$b81a9810$a301280a@.phx.gbl...
> When I tried to grant a Select permission on a table to a
> user, it was blocked by any users who were currently
> reading that table.
> The grant command in QA issued lock Sch-M (Schema
> modification) lock. Why is it issued Sch-M lock ?
> Is it mean I cannot grant permission only any table while
> users access it ?
>|||1) Why does grant permission on table require Sch-M lock ?
I am sure it is regular task to grant table permission to
users. I can't just wait for scheduling overnight.
2) I also tried to create a database role. But when I
grant Select permission to that role, it was blocked.
Running SQL 7 & SP3 on Windows 2000.|||Johnny,
1) You have a good point but the fact remain it does and your can do it
until you can lock the table. Think of it this way, if you were changing
the permissions to remove users (which may be accessing the Table) with
permissions. Then you can understand you would need to lock the schema.
The process doesn't know you are adding until after.
2) Yes the time you grant permissions to the role again you need a lock but
after what you do is add users to roles this doesn't require a lock at all.
So you can do this while the table is in use.
I hope this helps
regards
Greg O MCSD
http://www.ag-software.com/ags_scribe_index.asp. SQL Scribe Documentation
Builder, the quickest way to document your database
http://www.ag-software.com/ags_SSEPE_index.asp. AGS SQL Server Extended
Property Extended properties manager for SQL 2000
http://www.ag-software.com/IconExtractionProgram.asp. Free icon extraction
program
http://www.ag-software.com. Free programming tools
"Johnny" <jtao@.ssc.nsw.gov.au> wrote in message
news:049101c3b56e$5e304e80$a401280a@.phx.gbl...
> 1) Why does grant permission on table require Sch-M lock ?
> I am sure it is regular task to grant table permission to
> users. I can't just wait for scheduling overnight.
> 2) I also tried to create a database role. But when I
> grant Select permission to that role, it was blocked.
> Running SQL 7 & SP3 on Windows 2000.