Again :SQL Server Permission

[Elham GH]

Let me see explain my problem in another way.
Suppose I have Table which name is "Customer".
This Table has these Field:(CustomerCode,CompanyName,Address,Tel)

I create a Stored Procedure like this:
Create Stored procedure SpFectch_customer
Select CustomerCode,CompanyName,Address,Tel
From Customer

GO

Now, I create 2 users:User1,User2
User1 Permitted to Select all field of this table.
User2 permitted to select just CustomerCode,CompanyName fields.
And Both user can Excute SpFectch_customer.

Now here is my problem! When User2 excute this SP,he gets Error.
*How can execute one Stored Procedure by all users with different permissions?
*Should I Create several SP or view for each kind of permission?

Thanks

[John Bell]

Hi

From you description, it sounds like the ownership chain is somehow broken.
Using separate Stored procedures will be a nuisance when coding a UI, so you
may want to try something like:

Create procedure SpFectch_customer AS
Select
CASE WHEN PERMISSIONS(OBJECT_ID('Customer'),'CustomerCode') & 1 = 1 THEN
CustomerCode END AS CustomerCode ,
CASE WHEN PERMISSIONS(OBJECT_ID('Customer'),'CompanyName') & 1 = 1 THEN
CompanyName END AS CompanyName,
CASE WHEN PERMISSIONS(OBJECT_ID('Customer'),'Address') & 1 = 1 THEN Address
END AS Address,
CASE WHEN PERMISSIONS(OBJECT_ID('Customer'),'Tel') & 1 = 1 THEN Tel END AS
Tel
From Customer

GO

Alternatively you may want to create a view that selects the different
columns based on a role. This would also mean you don't have to grant
permissions on specific tables/columns if the ownership chain is not broken.

sp_addrole LimitedAccess
sp_addrolemember LimitedAccess, User2

DROP VIEW Customer_VW
CREATE VIEW Customer_VW
AS
Select CustomerCode, CompanyName, Address, Tel
From Customer1
WHERE IS_MEMBER('LimitedAccess') <> 1
UNION
Select CustomerCode, CompanyName, NULL,NULL
From Customer1
WHERE IS_MEMBER('LimitedAccess') = 1

Create procedure SpFectch_customer AS
Select CustomerCode , CompanyName, Address, Tel
From Customer_VW

John

"Elham GH" <ElhamGh_...@yahoo.com> wrote in message
news:e045d91.03052...@posting.google.com...