Link to home
Create AccountLog in
Avatar of Lorna70
Lorna70

asked on

SQL Newbie - Granted execute permissions to all sprocs and now older sprocs have stopped running.

Hi
I had some sprocs on my DB set up to enable SQL Experss backup scheduling as per:
http://www.sqlteam.com/article/scheduling-jobs-in-sql-server-express
I then ran a the following T-SQL to Grant execute permissions to all stored procedures in this DB:
USE DBS_New
GRANT EXECUTE TO New_DBS
Now the scheduling as stopped working so I can only assume it's a permissions problem as it it was working fine before I ran this T-SQL.  What do I have to do now to get them to run again??  I'm not sure which user they were running under before - how do I find this out?
Avatar of Vitor Montalvão
Vitor Montalvão
Flag of Switzerland image

You can't grant execute to all stored procedures with one single command.
>>You can't grant execute to all stored procedures with one single command.<<
You may want to double check that.  But that is not what the author is asking.
My comment it was about: "GRANT EXECUTE TO New_DBS"
What should that do?
Ah.  I see your point.
Avatar of Lorna70
Lorna70

ASKER

I thought you could grant exec to all sprocs at once with that command in SQL 2008 and I thought it had worked (didn't return errors).  However, I've checked permissions now and it doesn't look like it has granted permissions to all sprocs so I will do it individually.  Nobody has actually answered my question yet though - what login should the scheduling scripts be run under?
ASKER CERTIFIED SOLUTION
Avatar of Vitor Montalvão
Vitor Montalvão
Flag of Switzerland image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
SOLUTION
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
acperkins, it's SQL Server Express Edition. Doesn't have Agent service.
Did you post in the wrong thread?  What has SQL Server Agent got to do with setting permissions?  We are talking about T-SQL here.

Or am I missing something ?
SOLUTION
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account