May 19, 2017 at 7:47 am
We have an external user (who works for a different company) who needs access on our prod server. I am creating an account for him (SQL Server authentication) with read only access on tables. DO I need to get anything else done on my side or just creating his account with SQL Server authentication will allow him to access the DB?
May 19, 2017 at 7:59 am
You need to create the login and user. If it were me, I'd grant SELECT privs on the tables and functions he needs and nothing more. The principle of least privileges should be applied everywhere it can and this is no exception.
May 19, 2017 at 8:10 am
I'd restrict his access to the databases too. I won't allow him to view the tables/objects of all databases, but only to those specified.
Igor Micev,My blog: www.igormicev.com
May 19, 2017 at 9:44 am
Thanks for the reply. I have all the details on which tables he should have access to and it is read only access on 4 tables in a DB. I am creating his account by expanding a DB, security and then user or should I expand the security and then create his login?
May 19, 2017 at 10:04 am
This is where I am confused and I will try to keep it simple.
1st scenario: I create a login with no access to DB. So when I run the grant select on table, it says user doesn't exist.
2nd scenario: I create a login with db_datareader on the db and then I run grant select statement, it says command runs successfully.
My confusion is that when I create a user with db_datareader access, doesn't it automatically allow user to run select against any table? So how do I restrict him to run select against certain objects because I don't want him to query just any table?
May 19, 2017 at 10:27 am
You need both the login and the user setup. Login is at an instance level, and user is at a database level. If you use the built in role db_datareader, then yes, that user will be able to read from any table in that database. You can make your own database role to assign permissions to, and then assign the user to that role. Sometimes it's easier to script it out than clicking all over the place in Management Studio:
CREATE ROLE [Reporting];
GRANT SELECT ON dbo.TableName1 TO [Reporting];
GRANT SELECT ON dbo.TableName2 TO [Reporting];
GRANT SELECT ON dbo.TableName3 TO [Reporting];
GRANT SELECT ON dbo.TableName4 TO [Reporting];
EXEC sp_addrolemember 'Reporting', 'someusername';
May 19, 2017 at 10:36 am
It's all good. I created a login, selected the DB and didn't assign any role, then ran SQL statements to grant him read only access. Thanks for the help.
May 20, 2017 at 7:07 am
newdba2017 - Friday, May 19, 2017 10:36 AMIt's all good. I created a login, selected the DB and didn't assign any role, then ran SQL statements to grant him read only access. Thanks for the help.
Did you grant the user the db_datareader role or did you grant select on the four tables it needs?
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply