SA Developer .NET

Hi,

I need to restrict / grant access to multiple stored procedures in one script. Basically I want to give rights to a the website calling the stored procedures but the website must not have access to the base tables.

Whats the quickest and cleanest way to do this all in one go dynamically for all sp's?

---

SA Developer .Net Online Community Support
Please remember to click “Mark as Answer” on the post that helps you, and to click “Unmark as Answer” if a marked post does not actually answer your question.

I would write a query to generate the grant script. Something like this:

select 'GRANT EXECUTE ON ' + name + ' TO WebUser' from sys.procedures

Then take what it produces and run it. Since you don't want access to the base tables, consider adding the user to the db_denydatareader and  db_denydatawriter

If you are going to need to do this for more than one user (or you foresee that in the future), create a database role, grant the permissions to the role and assign the web user to that role.

---

Gail Shaw - SQL In the Wild
SQL Server MVP
--
Chaos, panic and disorder. My job here is done!

create procedure AllowSPExec
    @UserName varchar(50)
as

begin

declare myCursor cursor for
select specific_name from INFORMATION_SCHEMA.ROUTINES

declare
    @ProcedureName varchar(150),
    @execSql varchar(2000)

open myCursor

fetch next from myCursor into @ProcedureName
while @@fetch_status = 0
begin
    set @execSql = 'grant execute on ' + @ProcedureName + ' to ' + @UserName
    EXEC(@execSql)
    fetch next from myCursor into @ProcedureName
end

close myCursor
deallocate myCursor

end

---

"I would love to change the world, but they won't give me the source code"
Meeting Place - chat online with anyone, anytime, no downloads or plugins required

Thanks for the awesome and prompt assistance!

---

SA Developer .Net Online Community Support
Please remember to click “Mark as Answer” on the post that helps you, and to click “Unmark as Answer” if a marked post does not actually answer your question.

Sorry, just one change, you don't want to give them access to the proc that gives them access:

ALTER procedure [dbo].[AllowSPExec]
    @UserName varchar(50)
as

begin

declare myCursor cursor for
select specific_name from INFORMATION_SCHEMA.ROUTINES

declare
    @ProcedureName varchar(150),
    @execSql varchar(2000)

open myCursor

fetch next from myCursor into @ProcedureName
while @@fetch_status = 0
begin
    if @ProcedureName <> 'AllowSPExec'
    begin
        set @execSql = 'grant execute on ' + @ProcedureName + ' to ' + @UserName
        print @execSql
        EXEC(@execSql)
    end
    fetch next from myCursor into @ProcedureName
end

close myCursor
deallocate myCursor

end
 

---

"I would love to change the world, but they won't give me the source code"
Meeting Place - chat online with anyone, anytime, no downloads or plugins required