• Status: Solved
  • Priority: Low
  • Security: Public
  • Views: 37
  • Last Modified:

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
0
hidrau
Asked:
hidrau
  • 4
  • 2
  • 2
2 Solutions
 
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
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
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
 
hidrauAuthor Commented:
thanks a lot
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

  • 4
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now