?
Solved

SQL user

Posted on 2010-01-11
12
Medium Priority
?
241 Views
Last Modified: 2012-05-08
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
Comment
Question by:mcrmg
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 5
  • 2
12 Comments
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 26285142
that seems to be fine
0
 

Author Comment

by:mcrmg
ID: 26285196
I am getting this error

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

thx
0
 
LVL 75

Assisted Solution

by:Aneesh Retnakaran
Aneesh Retnakaran earned 180 total points
ID: 26285303
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
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 
LVL 4

Expert Comment

by:jlsilva01
ID: 26285315
You can run:

GRANT EXECUTE TO login ;
0
 

Author Comment

by:mcrmg
ID: 26285328
DO I have to do this to every sp? thx
0
 
LVL 4

Expert Comment

by:jlsilva01
ID: 26285359
If you do a login, you don't need to do to each proc.
0
 
LVL 4

Expert Comment

by:jlsilva01
ID: 26285378
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
 

Author Comment

by:mcrmg
ID: 26285389
where can I set it?  thx
0
 
LVL 4

Accepted Solution

by:
jlsilva01 earned 320 total points
ID: 26285419
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
 

Author Comment

by:mcrmg
ID: 26285460
I see, there is no way to do this via GUI?  thx
0
 
LVL 4

Expert Comment

by:jlsilva01
ID: 26285538
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
 

Author Comment

by:mcrmg
ID: 26285645
thanks again..
0

Featured Post

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have written a PowerShell script to "walk" the security structure of each SQL instance to find:         Each Login (Windows or SQL)             * Its Server Roles             * Every database to which the login is mapped             * The associated "Database User" for this …
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question