Security question

[sparkbyte]

I just had a DB resort back to default security setting after a failed maintenance process. (Production too)

While resetting the security on the DB I noticed that the uSP's would not run for normal users.

Found that users have to have execute on the uSP to be able to run it.

For a general use DB All users should be able to run any uSP created for the DB why not just add execute to the DBO schema?

Or is there another way to add execute prv's to the uSP's whithout being that global?



Thanks

John Fuhrman

 

[mrdenny]

What sort of maintenance were you doing that permissions changed?

If you need to grant execute rights for users to all stored procedures then you either need to grant execute to every procedure, or to the next level up which would be to the schema.

Denny
MVP
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / SQL 2005 BI / SQL 2008 DBA / SQL 2008 DBD / SQL 2008 BI / MWSS 3.0: Configuration / MOSS 2007: Configuration)
MCITP (SQL 2005 DBA / SQL 2008 DBA / SQL 2005 DBD / SQL 2008 DBD / SQL 2005 BI / SQL 2008 BI)

My Blog

 

[sparkbyte]

Thanks Denny.

I ended up creating a new Database role and added the uSP's to the Role with Execute and then added the AD security group to the new role.

Thanks!!!

Thanks

John Fuhrman