asked on

adding a user with execute permissions

Hello. I am in need of adding a new User to my database. I am wondering if there is a way to loop through all of the Stored Procs I have created and add execute permissions for this user on those procs. I have a ton of procs, so I was wanting to do a loop through each proc instead of writing a script that does this to each and everyone, which would take me a good bit of time!

Thanks,
jrmcdona

arbert

Quick and dirty--change username to the username you want to grant execute rights to:

create table #routines (
routine_name varchar(1000),
rownumber int identity(1,1)
)

insert into #routines
select routine_name from information_schema.routines

declare @routine varchar(1000)
declare @user varchar(200)
declare @sql varchar(1000)
declare @lastrow int
declare @row int
select @user='username'

select @routine =''
select @lastrow=0
select @row=0

while @routine is not null
begin
select @routine=routine_name,@row=rownumber from #routines
where rownumber=@lastrow+1

select @sql='grant execute on ' + @routine + ' to ' + @user
exec (@sql)
if @row=@lastrow
begin
break
end

select @lastrow=@row
end

drop table #routines

SOLUTION

arbert

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER CERTIFIED SOLUTION

Anthony Perkins

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

jrmcdona

ASKER

Thanks guys!
I will give you both points.

However, I just added a new WebUser role to the database and assigned that role Execute permissions to the stored procs.
I then created a user and assigned the WebUser role to the new user. Leaving the new user with public/WebUser roles.

I tried logging into my web app and login failed right off! If I can get this to work, I do see where granting execute permissions to roles and assigning users to roles
is much easier!

Any ideas?

thanks,
Jordan

arbert

Agree with AC--role based is positively easier....

What web technology you using (.NET, CF, Etc?)

jrmcdona

ASKER

ASP/ASP.NET

I changed my connection string to my new user and it failed to execute the login query. Either that or it failed to login to the database altogether. I gave the role execute permissions to all the procs.

thanks

arbert

How do you have your .NET security setup? using the ASP_NET account? Impersonation?

Anthony Perkins

Try logging on to SQL Query Analyzer as the new user and see if you can execute the stored procs.

jrmcdona

ASKER

Ok, I am now able to login and execute procs.

Do I need to added Select/insert/update/delete to all of the tables?

For instance; I took two tables and added "select" permissions to them and that was all. I was able to run an insert on one of the tables and not the other. I am not sure why that is.

Would it be safe to just add select/insert/update/delete to all of the tables??

thanks,
Jordan

arbert

"Would it be safe to just add select/insert/update/delete to all of the tables??"

Best practices usually dictate that nobody has direct access to tables. Updates happen through procs, selects go against views.....

jrmcdona

ASKER

I add EXEC permissions on all of the procs for this role i created. However i was getting SELECT permissions against the tables? For some reason the EXEC permissions were not enough? I must be missing a step somwhere?

Anthony Perkins

>> I must be missing a step somwhere?<<
Yes. It is called dyanamic SQL. If you are using it in a Stored Procedure, you have two options:
1. Get rid of it (recommended).
2. You will need to add Select permissions to the tables.

jrmcdona

ASKER

Gotcha! The first option will not be possible in this application anytime soon!

thanks for all of the help.

Jordan