• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 84
  • Last Modified:

Granting access to Microsoft SQL Server

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
rayluvs
Asked:
rayluvs
  • 9
  • 7
2 Solutions
 
neehar gollapudiCommented:
create a group and add roles to that group then you can start adding the logins or usernames.
0
 
lcohanDatabase AnalystCommented:
"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
 
rayluvsAuthor Commented:
'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
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
rayluvsAuthor Commented:
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
 
lcohanDatabase AnalystCommented:
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
 
rayluvsAuthor Commented:
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
 
lcohanDatabase AnalystCommented:
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
 
rayluvsAuthor Commented:
Went to properties of the table an yes, it's DBO.

dbo
The problem is one table, the above table.
0
 
lcohanDatabase AnalystCommented:
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
 
rayluvsAuthor Commented:
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
 
rayluvsAuthor Commented:
FYI,

Added "db_executor" as suggested, still no access.
0
 
lcohanDatabase AnalystCommented:
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
 
rayluvsAuthor Commented:
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
 
lcohanDatabase AnalystCommented:
The error means that the user cannot login to that SQL server which is way before "accesing" the table.
0
 
rayluvsAuthor Commented:
Oh ok, understood.

What are we missing to make the user to connect to the SQL server?
0
 
lcohanDatabase AnalystCommented:
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
 
rayluvsAuthor Commented:
Reading all the entries, the question has been answer.

This situation we will place another question.

Thanx!
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 9
  • 7
Tackle projects and never again get stuck behind a technical roadblock.
Join Now