View Permissions

Question

Post reply

View Permissions

kwoznica

SSCertifiable

Points: 5275
More actions
June 20, 2011 at 12:59 pm

#240037

I have created a view for the purchasing department in my company to use. I am then going to show them how to connect to the view in Access. Currently the database is in mixed mode sql authentication.
How do I set permissions on the view so only select permissions are granted? I want the view to be read only.
Also I would like to use their windows account on the view, currently end users authenticate through the front end application using sql authentication. Do I need to add select permissions to the underlying table of the view?
Thanks.

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

RP_DBA SSCertifiable Points: 5116 More actions · Answer 1

You should just have to grant them access to the db and then grant them select permissions on the view.

USE [DATABASE]

GO

CREATE USER FOR LOGIN

GO

GRANT SELECT ON [SCHEMA].[VIEW] TO

GO

_____________________________________________________________________
- Nate

@nate_hughes

kwoznica SSCertifiable Points: 5275 More actions · Answer 2

RP_DBA (6/20/2011)
You should just have to grant them access to the db and then grant them select permissions on the view.
USE [DATABASE]
GO
CREATE USER FOR LOGIN
GO
GRANT SELECT ON [SCHEMA].[VIEW] TO
GO

Awesome. Thanks Nate. Then if i want to remove their permissions how would that be done? I tried using a DROP and Delete statement but they both failed.

kwoznica SSCertifiable Points: 5275 More actions · Answer 3

kwoznica

SSCertifiable

Points: 5275

June 20, 2011 at 3:34 pm

#1341483

Nevermind. I got it with Revoke Select ON....

Michael L John One Orange Chip Points: 27060 More actions · Answer 4

It may be a bit cleaner to add (or create) the Active Directory group that corresponds to the purchasing department to SQL Server, and grant execute to the group as opposed to individual users.

This is certainly a lot simpler to maintain, and becasue of this, is more secure.

Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/

kwoznica SSCertifiable Points: 5275 More actions · Answer 5

Michael L John (6/20/2011)
It may be a bit cleaner to add (or create) the Active Directory group that corresponds to the purchasing department to SQL Server, and grant execute to the group as opposed to individual users.
This is certainly a lot simpler to maintain, and becasue of this, is more secure.

Thats a great idea Michael. I will do just that. Thanks for chiming in.

Steve Jones - SSC Editor SSC Guru Points: 736504 More actions · Answer 6

The AD group is a good idea, but you should also have a role (group) in SQL Server for different permissions. Don't grant permissions to users, always use a role.

Michael L John One Orange Chip Points: 27060 More actions · Answer 7

Steve finished my thought.

Try to plan ahead. Most entities in a database follow a "pattern" of logical roles. For example, the marketing people probably do not need to see the same data as human resources. The tables in the database likely follow this pattern. Creation of views, and controlling access with the views, may work fine today. But it may become a full time job down the road!

Keep nested users and groups to a minimum, if possible.

Without some forethought and planning, blindly creating roles and granting/revoking permissions will be a bigger headache in the future. If purchasing needs access today, you can bet that other business units will also need access tomorrow.

Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/

kwoznica SSCertifiable Points: 5275 More actions · Answer 8

Thanks for the feedback. I finally set it up with an AD Group called purchasing, and added domain users to that ad group. Then I created a windows authenticated user in sql server with domain\purchasing and finally created a database role called purchasing and added the user from the SQL logins list.

Now when I go into access to see the data I am also seeing the dmv's and information schema views. Is there a way to make that data not appear when looking at the data in access?

I added an attachment of a print screen from access of the additional views i am seeing.

Steve Jones - SSC Editor SSC Guru Points: 736504 More actions · Answer 9

Those are viewable, IIRC, from public permissions.

Some of those are needed for meta data to actually access the objects that you have granted permission for. Typically I have not worried about those and allowed people to see them. They don't usually contain anything that is useful to users.