Menu
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?
613
To grant permissions on a stored procedureExpand Stored Procedures, right-click the procedure to grant permissions on, and then select Properties. From Stored Procedure Properties, select the Permissions page. To grant permissions to a user, database role, or application role, select Search.
To grant permissions to a user, database role, or application role, select Search. In Select Users or Roles, select Object Types to add or clear the users and roles you want. Select Browse to display the list of users or roles. Select the users or roles to whom permissions should be granted.
Right click on your procedure and select Properties. You'll get the following window. As shown inthe preceding image, go to Permissions tab and click on Search button. On click you'll get a window to select user and roles, click on Browse to select users that require permission and click OK.
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;
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
