Solved

adding a user with execute permissions

Posted on 2004-04-13
13
368 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
  • 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
VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

 

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-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

Suggested Solutions

I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
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.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

770 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