April 25, 2017 at 8:17 am
I have created a user and a server role and assigned the role to the user.
I can now connect as that user to the DB,. I want to give access to only one view to that user / role.
I tried the following and it gives error - no process at the other end of the pipel.
grant select to schema.view to user
If I do the SQL below, I get an error that the DB is not accessible.
use DBName
grant select to user
How do I make this work?
April 25, 2017 at 8:55 am
The first line in your post is a bit confusing - "created a user and a server role and assigned the role to that user"
You would add a login to a server role and users would exist in databases.
Did you mean database role or server role? Do you mean user in the database or login for the server?
Could be that the mix up in one of those pieces is causing the problem.
Connect as yourself, not that user. Make sure the the login exists on the server which is seems it must since you connected as that user.
Add or make sure to add the login as a user for the database.
Grant select on view to the database user.
Sue
April 25, 2017 at 9:03 am
The correct SQL for something like this would be:--Start at master, as we will creating a LOGIN
USE master;
GO
--Create the LOGIN first
CREATE LOGIN SSCSample
WITH PASSWORD = N'test123',
DEFAULT_DATABASE = TestDB,
DEFAULT_LANGUAGE = British,
CHECK_EXPIRATION = OFF,
CHECK_POLICY = OFF;
GO
--Connect to the right Database
USE TestDB;
GO
--Create the USER for the LOGIN
CREATE USER SSCSample FOR LOGIN SSCSample;
GO
--Grant SELECT on VIEW/TABLE
GRANT SELECT ON dbo.test TO SSCSample;
GO
/*
--Clean Up
DROP USER SSCSample;
GO
DROP LOGIN SSCSample;
GO
*/
As Sue outlined, notice that I create a Login first, and then the user on the specific database. I skipped creating a role on the database as this was a simple example.
My question, however, is actually related to your error: no process at the other end of the pipe. Are you actually connected to the SQL server before running this SQL? This sounds like your using named pipes for the connection to your SQL server however, it isn't enabled correctly, thus the connection is failing. it could also be due to some poorly formed SQL prior to the SQL posted though.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
April 25, 2017 at 11:40 am
Thanks that worked.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply