Thanks for sharing the script.

Is there a reason you do not create a database role, grant execute permissions to the role, and then simply add the new users to the database role? That would greatly simplify things.

Joe

If you really want to do things the right way, your should have 1 user account for your application to connect to and then have a user privlages table to lookup authorizations for each individual rather than having sql server accounts for each user. More coding in your app yes, but based on getting burned by a badly developed application at my place of employment we forever went this rout. IE your application should know what parts of the application a given user should have access to. The application has read and execute on all the objects it uses.

IE: don't over-use sql server security by having a sql server account for each user of the application - that's a huge security risk because now they have access to your database...It's a mistake I've seen a lot of folks do, including us :)

What we do is have screens developed to add, edit, delte new and existing users and check boxes next to various things they are able to do. Say you want Bob and Cathy to have access to the Financials screen(s) then you can easily handle that now by checking before you display. On top of that, you no longer have to bug your dba to add the user as a sql account since they don't need one(and shouldn't for any application). Remember that a slq server user account is supposed to be only for people that are going to be on the database directly such as a dba or developer. When you think about it, nobody else, including support should have direct access to the db, everyone else should be going through the application. Building in backend support to an application most of us don't but should do.

Make sense?

Dave

Hi Dave,

What you say perfectly make sense. even I use the same methodology for putting authorization in my application.

But I think you missed other scenario's of which I was thinking when I wrote this Post. Lets say you have developped an application and aftre uploading you want to create a new SQL user(in the production server). Now you have 300 stored procedure to give execute permission to this new SQL user. The application still uses only SQL server user.

Also the requirement of changing the user for the applictaion might come for many unseen reason. The post talked about how to easily provide permission to that new sql user for all stored procedure in the SQL.

Vikram

how can i give execute permissions to all the store procedures to particular existing users on particular database

select 'Grant Execute on ' + name + ' to [insertUserNameHere]'
from sysobjects where xtype in ('P')

select 'Grant Execute on [' + name + '] to [insertUserNameHere]'
from sysobjects where xtype in ('P')

It was a nice script..!!

For example:

select 'Grant Execute on ' + name + ' to johnsmith'
from sysobjects where xtype in ('P')

Hi,
I got this & it is to helpfull to me so thanks lot but
i have to create permmision name as "public" for this grant to all users

GRANT EXECUTE to [User]
GO

might be another option.