Solved

Granting access to Microsoft SQL Server

Posted on 2016-11-04
17
28 Views
Last Modified: 2016-11-04
Hi,

We want users to access all the tables within a specific database in MS SQL 2014 Server. The DB will be used by diferent apps.  The apps open, modify, deletes and create records within the tables.

Please advice on the optimum steps to follow for a successful permisión granting.
0
Comment
Question by:rayluvs
  • 9
  • 7
17 Comments
 
LVL 1

Expert Comment

by:neehar gollapudi
ID: 41874271
create a group and add roles to that group then you can start adding the logins or usernames.
0
 
LVL 39

Expert Comment

by:lcohan
ID: 41874274
"We want users to access all the tables within a specific database in MS SQL 2014 Server" - are all these users AD or windows login users or SQL logins as well?

If they are ALL AD/Windows logins the best would be to add them all in a Group(or groups if they need different level of access)  and grant ALL needed permissions to the group via a standard database role.

Other than that you could grant db_datareader/db_datawriter to each user and that would be suficient to "..."modify, deletes and create records within the tables. as you requested.
0
 

Author Comment

by:rayluvs
ID: 41874358
'are all these users AD or windows login users or SQL logins as well?'

The users are created in the Server and all uses the server via Remote Desktop; each have their own individual login to the server.

' best would be to add them all in a Group'

please provide the steps.

'you could grant db_datareader/db_datawriter to each user'

please provide the steps.
0
 

Author Comment

by:rayluvs
ID: 41874371
We think we got it:

EXEC sp_addrolemember N'db_datareader', N'your-user-name'
EXEC sp_addrolemember N'db_datawriter', N'your-user-name'
GRANT SELECT, INSERT, UPDATE ON table TO user

and

Database >> properties >> database member role >> Checkmark db_datareader
Database >> properties >> database member role >> Checkmark db_datawriter

Missing anything (so we can execute)?
(theses are the steps)
0
 
LVL 39

Accepted Solution

by:
lcohan earned 500 total points
ID: 41874380
if you run the commands below:
EXEC sp_addrolemember N'db_datareader', N'your-user-name'
EXEC sp_addrolemember N'db_datawriter', N'your-user-name'
in each database then you don't need to
GRANT SELECT, INSERT, UPDATE ON table TO user
as that's exactly what they do at the database level and also you don't need to use the UI to
Database >> properties >> database member role >> Checkmark db_datareader
Database >> properties >> database member role >> Checkmark db_datawriter
because again - that's the exact same thing done via the UI so all you will need is to:

EXEC sp_addrolemember N'db_datareader', N'your-user-name'
EXEC sp_addrolemember N'db_datawriter', N'your-user-name'

for all users in each of the DB they need to "SELECT, INSERT, UPDATE ON table" and even DELETE.
0
 

Author Comment

by:rayluvs
ID: 41874398
Just finish setting it,

EXEC sp_addrolemember N'db_datareader', N'username'
EXEC sp_addrolemember N'db_datawriter', N'username'
GRANT SELECT, INSERT, UPDATE ON tablename TO username

 and the users don't have access to a specific table.
0
 
LVL 39

Expert Comment

by:lcohan
ID: 41874403
what schema is that table in? is it DBO or some other schema although...
EXEC sp_addrolemember N'db_datareader', N'username'
EXEC sp_addrolemember N'db_datawriter', N'username'
this must be sufficient IN THAT particular DB so maybe you did not selected the DB first?

try:

USE yourDBnameHere;
GO
EXEC sp_addrolemember N'db_datareader', N'username'
EXEC sp_addrolemember N'db_datawriter', N'username'
0
 

Author Comment

by:rayluvs
ID: 41874421
Went to properties of the table an yes, it's DBO.

dbo
The problem is one table, the above table.
0
Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

 
LVL 39

Assisted Solution

by:lcohan
lcohan earned 500 total points
ID: 41874464
What is the exact error and are all users experiencing the same issue?
Is it possible that the table "action" is done via some SQL code object in which case the "execute" permissions are denied not the read/write? if that's the case you would need to add a new role like "db_executor" below and grant rights against it so users can call/execute SQL code objects besides just adhoc data changes:

USE yourDBnameHere;
GO
CREATE ROLE db_executor
GRANT EXECUTE ON SCHEMA::[dbo] TO db_executor
--GRANT EXECUTE ON SCHEMA::[admin] TO db_executor -- this is to grant EXEC rights on ANY other schema if needed
exec sp_addrolemember 'db_executor', 'username'
GO
0
 

Author Comment

by:rayluvs
ID: 41874474
What is the exact error and are all users experiencing the same issue?

Error number: -214721783
Login failed for user SqlInstanceName\UserName


Yes, all non-administrator users (Admins has no problem with this table)

Is it possible that the table "action" is done via some SQL code object in which case the "execute" permissions are denied not the read/write?

If 'via some SQL code object' you mean via the actual apps, yes (the user don't log into the SSMS).

Based on what we stated above, should we still add "db_executor" as suggested?
0
 

Author Comment

by:rayluvs
ID: 41874484
FYI,

Added "db_executor" as suggested, still no access.
0
 
LVL 39

Expert Comment

by:lcohan
ID: 41874661
The error you posted above "Error number: -214721783
Login failed for user SqlInstanceName\UserName" has absolutely nothing to do with permissions/access to perform DML changes on whatever you call "that table". That message tell it all and that "Login failed for user SqlInstanceName\UserName" not that the login cannot SELECT, INSERT, UPDATE, or DELETE from that table.
0
 

Author Comment

by:rayluvs
ID: 41874688
Reading your trying to understand the difference; we thought "Login failed for user" was because the cannot access the table.

So what does the error actually mean?
0
 
LVL 39

Expert Comment

by:lcohan
ID: 41874704
The error means that the user cannot login to that SQL server which is way before "accesing" the table.
0
 

Author Comment

by:rayluvs
ID: 41874749
Oh ok, understood.

What are we missing to make the user to connect to the SQL server?
0
 
LVL 39

Expert Comment

by:lcohan
ID: 41874754
Well "Login failed for user" means that whatever username was used to login to that SQL on that connection is wrong or password for that user (asuming it exists in SQL) was incorrect.
0
 

Author Comment

by:rayluvs
ID: 41874957
Reading all the entries, the question has been answer.

This situation we will place another question.

Thanx!
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Auditing with Temporal Tables 4 19
T-SQL Using IN with a subquery 3 12
Slow SQL query 12 27
How toselect unique values 3 10
Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.

746 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now