How to create a user and allow it only to see some views in mssql 2008?

Hello guys,

I need to create a user and allow it only to see some views,

How could I do that?

thanks
Alex
LVL 1
hidrauAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Pawan KumarDatabase ExpertCommented:
You can use below script -

USE YourDatabaseName
GO

CREATE USER yourUserName FROM LOGIN yourLoginName;
GO

GRANT SELECT ON Schema.YourViewName1 TO yourUserName 
GO

GRANT SELECT ON Schema.YourViewName2 TO yourUserName 
GO

Open in new window

..
0
hidrauAuthor Commented:
Hi,

My loggin is sa

I did this way:

USE [master]
GO
CREATE LOGIN [alexteste] WITH PASSWORD=N'aqz1973aqz', DEFAULT_DATABASE=[HermesQualidade1], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
GRANT SELECT ON Schema.ApuracaoImpostos TO [alexteste]
GO
GRANT SELECT ON Schema.ApuracaoImpostosUF TO [alexteste]
GO

and got this message:

Msg 15151, Level 16, State 1, Line 5
Cannot find the object 'ApuracaoImpostos', because it does not exist or you do not have permission.
0
Pawan KumarDatabase ExpertCommented:
ApuracaoImpostos - Is this View / Table ? This is not created .
0
The Five Tenets of the Most Secure Backup

Data loss can hit a business in any number of ways. In reality, companies should expect to lose data at some point. The challenge is having a plan to recover from such an event.

hidrauAuthor Commented:
it is a view
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
My loggin is sa
sa is an administrator user. You don't need to give him extra permissions as he has administrative rights, meaning that he can access everything in SQL Server.
0
hidrauAuthor Commented:
Ok,

I have four database named:

House
StoreHouse
StoreShop
Store

I want to create a user that can login only in house database
and can see only two views

this is my hard task today.
I created a user in my MASTER
I gave to the new user only the house database permition to open it
But it can see all the tables and views :(
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
First you need to create a login and then create an user from the login in the desired database and at last grant him the required permissions:
USE master
CREATE LOGIN LoginNameHere WITH PASSWORD = 'PasswordHere>'
GO

USE House
CREATE USER UserNameHere FOR LOGIN LoginNameHere WITH DEFAULT_SCHEMA=[dbo]

GRANT SELECT ON ViewName1, ViewName2 TO UserNameHere

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
hidrauAuthor Commented:
thanks a lot
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.