Solved

adding a user with execute permissions

Posted on 2004-04-13
13
365 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
 

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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
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

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

In this article—a derivative of my DaytaBase.org blog post (http://daytabase.org/2011/06/18/what-week-is-it/)—I will explore a few different perspectives on which week today's date falls within using Microsoft SQL Server. First, to frame this stu…
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

760 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now