Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Granting access to Microsoft SQL Server

Posted on 2016-11-04
17
Medium Priority
?
75 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 2

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 40

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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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 40

Accepted Solution

by:
lcohan earned 2000 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 40

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
 
LVL 40

Assisted Solution

by:lcohan
lcohan earned 2000 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 40

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 40

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 40

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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

926 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