Solved

adding a user with execute permissions

Posted on 2004-04-13
13
370 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
Edgartown IT Case Study

Learn about Edgartown's quest to ensure the safety and security of the entire town's employee and citizen data. Read the case study!

 

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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

I have a large data set and a SSIS package. How can I load this file in multi threading?
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
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…

756 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