Solved

adding a user with execute permissions

Posted on 2004-04-13
13
371 Views
Last Modified: 2012-05-04
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
0
Comment
Question by:jrmcdona
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 5
  • 3
13 Comments
 
LVL 34

Expert Comment

by:arbert
ID: 10819525
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
 
LVL 34

Assisted Solution

by:arbert
arbert earned 125 total points
ID: 10819559
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
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 125 total points
ID: 10819930
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
Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

 

Author Comment

by:jrmcdona
ID: 10824688
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
 
LVL 34

Expert Comment

by:arbert
ID: 10824733
Agree with AC--role based is positively easier....

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

Author Comment

by:jrmcdona
ID: 10824766
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
 
LVL 34

Expert Comment

by:arbert
ID: 10824805
How do you have your .NET security setup?  using the ASP_NET account?  Impersonation?
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 10824860
Try logging on to SQL Query Analyzer as the new user and see if you can execute the stored procs.
0
 

Author Comment

by:jrmcdona
ID: 10825474
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
 
LVL 34

Expert Comment

by:arbert
ID: 10825506
"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
 

Author Comment

by:jrmcdona
ID: 10825559
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 10825637
>> 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
 

Author Comment

by:jrmcdona
ID: 10825659
Gotcha! The first option will not be possible in this application anytime soon!

thanks for all of the help.


Jordan
0

Featured Post

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Viewers will learn how the fundamental information of how to create a table.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question