t-sql create user and grant execute on permission

Question

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.

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

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)

But exec (@permission) does not work. It gives

incorrect syntax near ';'.

How can I solve this?

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

Had exactly the same problem as original user, but for me it was bad characters embedded in the TSQL - I'm guessing from whatever source it was cut and pasted from.

Anyway depending on how much whitespace you have, just delete the whitespace between the words and replace with regular spaces.

Try all the other answers before this, it's fairly unlikely - I'm only adding it as it was so frustrating having 2 lines of TSQL that looked identical above/below each other, but resulted in different result messages when highlighted and run in Management Studio...

UPDATE: The bad characters were pasted from Microsoft Lync

Answer 3

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;