Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 245
  • Last Modified:

SQL user

Hi,

I need to setup a SQL user for frontend to connect.  What database role I should check?
It needs to be able to execute sp, insert new record, update/delete records.

I have checked db_datareader and db_datawriter and public

thx
0
mcrmg
Asked:
mcrmg
  • 5
  • 5
  • 2
2 Solutions
 
Aneesh RetnakaranDatabase AdministratorCommented:
that seems to be fine
0
 
mcrmgAuthor Commented:
I am getting this error

The EXECUTE permission was denied on the object 'st_aaa', database 'my_DB', schema 'dbo'.

thx
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
seems like that is a stored procedure,  you need to grant the EXECUTE permission for the sps to

GRANT EXEC ON spName to theUsername
0
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

 
jlsilva01Commented:
You can run:

GRANT EXECUTE TO login ;
0
 
mcrmgAuthor Commented:
DO I have to do this to every sp? thx
0
 
jlsilva01Commented:
If you do a login, you don't need to do to each proc.
0
 
jlsilva01Commented:
Sorry my English...

If you do execute for all logins, you don't need to do for each proc, cause all procs already grant to public.
0
 
mcrmgAuthor Commented:
where can I set it?  thx
0
 
jlsilva01Commented:
I use this script to create a new user and set the permissions:

CREATE LOGIN [LOGIN_NAME] WITH PASSWORD=N'XXXXX', DEFAULT_DATABASE=[DATABASE_NAME], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
CREATE USER [LOGIN_NAME] FOR LOGIN [LOGIN_NAME] WITH DEFAULT_SCHEMA=[dbo]
GO
EXEC sp_addrolemember 'db_datareader', [LOGIN_NAME]
GO
EXEC sp_addrolemember 'db_datawriter', [LOGIN_NAME]
GO
GRANT EXECUTE TO [LOGIN_NAME]
GO
0
 
mcrmgAuthor Commented:
I see, there is no way to do this via GUI?  thx
0
 
jlsilva01Commented:
yes...

You click with right button on database and choose properties. After this, you click on Permissions... click on user name on "user or roles box" and checkbox a grant to execute item.
database-properties.png
0
 
mcrmgAuthor Commented:
thanks again..
0

Featured Post

Free recovery tool for Microsoft Active Directory

Veeam Explorer for Microsoft Active Directory provides fast and reliable object-level recovery for Active Directory from a single-pass, agentless backup or storage snapshot — without the need to restore an entire virtual machine or use third-party tools.

  • 5
  • 5
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now