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
jrmcdonaAsked:
Who is Participating?
 
Anthony PerkinsConnect With a Mentor Commented:
You should not be granting execute permissions to users, but rather grating execute permissions to roles and assigning users to roles.  It makes life easier.
0
 
arbertCommented:
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

0
 
arbertConnect With a Mentor Commented:
Actually, change the above insert statement so you only get stored procs and not system objects too:

insert into #routines
select routine_name from information_schema.routines
where routine_name not like 'dt%'
0
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
jrmcdonaAuthor Commented:
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
0
 
arbertCommented:
Agree with AC--role based is positively easier....

What web technology you using (.NET, CF, Etc?)
0
 
jrmcdonaAuthor Commented:
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
0
 
arbertCommented:
How do you have your .NET security setup?  using the ASP_NET account?  Impersonation?
0
 
Anthony PerkinsCommented:
Try logging on to SQL Query Analyzer as the new user and see if you can execute the stored procs.
0
 
jrmcdonaAuthor Commented:
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
0
 
arbertCommented:
"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.....
0
 
jrmcdonaAuthor Commented:
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?

0
 
Anthony PerkinsCommented:
>> 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.
0
 
jrmcdonaAuthor Commented:
Gotcha! The first option will not be possible in this application anytime soon!

thanks for all of the help.


Jordan
0
All Courses

From novice to tech pro — start learning today.