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

permissions in proc

I have noticed that our procs have permissions for a given user id inside th
e
proc.
Once that user id gets dropped, all our procs would fail. What is the best
practice on assigning user permissions for the proc?
tia...Trisha wrote:
> I have noticed that our procs have permissions for a given user id
> inside the proc.
> Once that user id gets dropped, all our procs would fail. What is the
> best practice on assigning user permissions for the proc?
> tia...
Can you give an example of what you mean by "inside the proc"? Are you
saying that inside your procedures you have code that resembles:
If suser_sname() = 'dgreen'
Begin
-- Do stuff
End
Normally, you would grant execute rights to those security groups/users
that require access to the procedure using GRANT.
David Gugick
Imceda Software
www.imceda.com

Permission to see stored procs but not change or exec them?

In Server 2000, our developers can see our stored procs but not change them or run them. In 2005, our developers can not see them at all but claim they need to. I am new to 2005 and have not found a way to achieve this. I would like to grant them access to see the procs and read the code but not change it or execute it. Do you know how this can be done?

Thanks!

hi,

Grant "view definition" permission to user.

regards

sufian