t-sql create user and grant execute on permission for stored procedures

Question

Hi

I have a script which creates a database, stored procs, views, tables, udf. I want to include a script to create a user 'user_1' and give execute permission on the database.

update

I tried following to create grant exec command for all stored procs

use DB;
go

declare @permission varchar(max)

select @permission = COALESCE(@permission + '; ' + 'Grant Execute on ' + name +  ' user_1', 'Grant Execute on ' + name +  ' user_1')
from sysobjects where xtype in ('P')

exec (@permission)

go

But exec (@permission) does not work. It gives incorrect syntax near ';'. Pls. suggest.

Thanks vishal

Answer 1

Have you tried:

CREATE LOGIN TestUser WITH PASSWORD = 'TopSecret'
GRANT EXEC ON MyStoredProc TO TestUser

you can also "CREATE USER" if that's what you want.

Answer 2

Create Login: creates the server level login. Then... Create User: lets the Login account attach to your database. Then... Grant Execute To: grants execute rights to ALL of the sp's and functions in your db. Use "Grant Execute ON abc TO xyz" if you only want to grant rights to specific sps.

Create login abacadaba with password='ABVDe12341234';
Create user abacadaba for login abacadaba;
Grant Execute to abacadaba;