How do SQL views work? The basics please...

[OldSkoolSkater]

Hello

A user has requested that Enterprise Mgr be installed on his PC so he can get view tables of a particular db, but I don't want to give him access to all other dbs on the SQL server in question.
I hit upon the idea of views but know absolutely nothing about them. I get the idea that you create a view and users can only see info within that view, but do they have to have EM installed on their PC to see this view?
If EM has to be installed, is it just a case of creating a login for the user and then assosciating them with the view so that's all they get to see?

Cheers

Steve - Network Coordinating in the UK

 

[AccordingToDale]

With EM you can craete a login for the user and give them permission to particular db's only.

Dale

 

[Catadmin]

Steve,

You can definately give them access only to views. It is a granular permission.

There are several ways of doing this. The first is the Install Client Tools, grant the user specific DB login permissions, then grant him only specific Select rights on the granular level. Also, look at the fixed Database Roles DataReader, DataWriter, DenyDataReader and DenyDataWriter.

A second is create a DTS job that transforms the info the user wants to see into an exported file of some sort and send it to the user.

A third is to set up permissions and link the view or table to a front-end MS Access database and make sure the user has read-only permissions.

You can also create a link to another (other vendor) database, but you would have to see that vendor's specifications for setting up a linked databse.

Does that help?



Catadmin - MCDBA, MCSA
"If a person is Microsoft Certified, does that mean that Microsoft pays the bills for the funny white jackets that tie in the back???