Grant Execute Permission to a User on all non-system stored procedures

We could easily grant execute permission to a user on all stored procedures on a database as below.

But unfortunately, our database will be at risk since the user would have got access to almost all stored procedures including the system stored procedures in that database. Hence use below steps to grant access to non-system stored procedures securely.

  1. Run below script on the database and copy the output.
  2. Paste the output to a new query window.
  3. Add “USE <database name>” at the beginning of the pasted output code and execute.

Note: Values inside ‘<‘ and ‘>’ has to be replaced with your actual database name and user information where ever required.

Check SQL Authentication Login Account Password

Did you come across any situation where you wanted to make sure that the password you set for a SQL account is what you really wanted? Below is how you can validate the password for a particular account. With this methord, you can find out the forgotten password for a SQL account without causing any audit failures and by not changing any security settings.

SELECT name FROM sys.syslogins WHERE pwdcompare ('SomePasswordWhichYouKnow',password) =1

When you run the above query, you will get the list of accounts which has password set as SomePasswordWhichYouKnow.

This way, you can figure out what password was set while creating an account (SQL authentication account)

Error while executing a sql job “The EXECUTE permission was denied on the object ‘sp_start_job’,database ‘msdb’,schema’dbo’.

SOLUTION:

1) Go to MSDB– Security –Users — UserName — User Name Properties — Assigned the ‘TargetServerRole’ in msdb

2)     Run the query

select pr.name, dp.permission_name, dp.state_desc

 from   msdb.sys.database_permissions dp

 join   msdb.sys.objects o on dp.major_id = o.object_id

 join   msdb.sys.database_principals pr

 on dp.grantee_principal_id = pr.principal_id

 where  o.name = ‘sp_start_job’

 

3)     Grant  Execute permission to concern user to SP_Start_Job and SP_Stop_Job and it resolved the issue.