Permissions...

I have been looking for an example of applying permissions to all the
tables, view, stored procs, etc., in X sql database without much luck.
Clearly, it's simple to do for a single record but I have over 500 tables,
views, etc., that need to have permissions assigned for SELECT, INSERT,
UPDATE and DELETE for the tables (EXECUTE for the stored procs).
The must be an example that I am missing that allows me to assign all these
permissions quickly, we have a tool within our application but since I have
no permission currently set I cannot even get to that tool to run it.
Thanks again - I always get assistance from users here! It's the best part
about moving our app from VFP data to SQL data.
Chrisyou can put users in db_datareader and db_datawriter roles to get select,
insert, delete and update permissions.
"Chris Marsh" <cmarsh@.synergy-intl.com> wrote in message
news:%23tnwBCWXFHA.2348@.TK2MSFTNGP14.phx.gbl...
>I have been looking for an example of applying permissions to all the
>tables, view, stored procs, etc., in X sql database without much luck.
>Clearly, it's simple to do for a single record but I have over 500 tables,
>views, etc., that need to have permissions assigned for SELECT, INSERT,
>UPDATE and DELETE for the tables (EXECUTE for the stored procs).
> The must be an example that I am missing that allows me to assign all
> these permissions quickly, we have a tool within our application but since
> I have no permission currently set I cannot even get to that tool to run
> it.
> Thanks again - I always get assistance from users here! It's the best
> part about moving our app from VFP data to SQL data.
> Chris
>|||Hi,
Use the database level fixed roles db_datareader and db_datawriter to
assign SELECT, UPDATE, INSERT, DELETE previlages for all
TABLES/VIEW. But for stored procedure Execution rights there is no roles
available. Only way is to give Execute previlages individually.
GRANT EXEC on Proc_name to Username
If you have numerous procs then write a small script to query the sysobjects
table for Xtype ='P' to get all the procedure names and use the above Grant
statement inside a cursor.
Thanks
Hari
Sql Server Mvp
"Chris Marsh" <cmarsh@.synergy-intl.com> wrote in message
news:%23tnwBCWXFHA.2348@.TK2MSFTNGP14.phx.gbl...
>I have been looking for an example of applying permissions to all the
>tables, view, stored procs, etc., in X sql database without much luck.
>Clearly, it's simple to do for a single record but I have over 500 tables,
>views, etc., that need to have permissions assigned for SELECT, INSERT,
>UPDATE and DELETE for the tables (EXECUTE for the stored procs).
> The must be an example that I am missing that allows me to assign all
> these permissions quickly, we have a tool within our application but since
> I have no permission currently set I cannot even get to that tool to run
> it.
> Thanks again - I always get assistance from users here! It's the best
> part about moving our app from VFP data to SQL data.
> Chris
>|||Thank you!!!!!
"Hari Pra" <hari_pra_k@.hotmail.com> wrote in message
news:exHE4XWXFHA.628@.tk2msftngp13.phx.gbl...
> Hi,
> Use the database level fixed roles db_datareader and db_datawriter to
> assign SELECT, UPDATE, INSERT, DELETE previlages for all
> TABLES/VIEW. But for stored procedure Execution rights there is no roles
> available. Only way is to give Execute previlages individually.
> GRANT EXEC on Proc_name to Username
> If you have numerous procs then write a small script to query the
> sysobjects table for Xtype ='P' to get all the procedure names and use the
> above Grant
> statement inside a cursor.
> Thanks
> Hari
> Sql Server Mvp
>
> "Chris Marsh" <cmarsh@.synergy-intl.com> wrote in message
> news:%23tnwBCWXFHA.2348@.TK2MSFTNGP14.phx.gbl...
>|||Thank you!!!!
"Richard Ding" <rding@.acadian-asset.com> wrote in message
news:eHa6nNWXFHA.2128@.TK2MSFTNGP14.phx.gbl...
> you can put users in db_datareader and db_datawriter roles to get select,
> insert, delete and update permissions.
>
> "Chris Marsh" <cmarsh@.synergy-intl.com> wrote in message
> news:%23tnwBCWXFHA.2348@.TK2MSFTNGP14.phx.gbl...
>sql