Re: sql2k5 security

OK. Here is my setup

USE [WS_Products]

GO

/****** Object: Table [dbo].[ProductCompanies] Script Date: 06/27/2006
00:56:18 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_PADDING ON

GO

CREATE TABLE [dbo].[ProductCompanies](

[companyid] [int] IDENTITY(1,1) NOT NULL,

[companycode] [varchar](20) COLLATE Latin1_General_CI_AI NOT NULL CONSTRAINT
[DF_ProductCompanies_companycode] DEFAULT (''),

[companyname] [varchar](100) COLLATE Latin1_General_CI_AI NOT NULL
CONSTRAINT [DF_ProductCompanies_companyname] DEFAULT (''),

[legalbusinessname] [varchar](100) COLLATE Latin1_General_CI_AI NOT NULL
CONSTRAINT [DF_ProductCompanies_legalbusinessname] DEFAULT (''),

[address1] [varchar](100) COLLATE Latin1_General_CI_AI NOT NULL CONSTRAINT
[DF_ProductCompanies_address1] DEFAULT (''),

[address2] [varchar](100) COLLATE Latin1_General_CI_AI NOT NULL CONSTRAINT
[DF_ProductCompanies_address2] DEFAULT (''),

[city] [varchar](50) COLLATE Latin1_General_CI_AI NOT NULL CONSTRAINT
[DF_ProductCompanies_city] DEFAULT (''),

[state] [varchar](30) COLLATE Latin1_General_CI_AI NOT NULL CONSTRAINT
[DF_ProductCompanies_state] DEFAULT (''),

[zipcode] [varchar](10) COLLATE Latin1_General_CI_AI NOT NULL CONSTRAINT
[DF_ProductCompanies_zipcode] DEFAULT (''),

[phone1] [varchar](20) COLLATE Latin1_General_CI_AI NOT NULL CONSTRAINT
[DF_ProductCompanies_phone1] DEFAULT (''),

[phone2] [varchar](20) COLLATE Latin1_General_CI_AI NOT NULL CONSTRAINT
[DF_ProductCompanies_phone2] DEFAULT (''),

[fax1] [varchar](20) COLLATE Latin1_General_CI_AI NOT NULL CONSTRAINT
[DF_ProductCompanies_fax1] DEFAULT (''),

[fax2] [varchar](20) COLLATE Latin1_General_CI_AI NOT NULL CONSTRAINT
[DF_ProductCompanies_fax2] DEFAULT (''),

[isapproved] [bit] NOT NULL CONSTRAINT [DF_ProductCompanies_isapproved]
DEFAULT ((1)),

[isintegrated] [bit] NOT NULL CONSTRAINT [DF_ProductCompanies_isintegrated]
DEFAULT ((0)),

[lenderid] [varchar](25) COLLATE Latin1_General_CI_AI NOT NULL CONSTRAINT
[DF_ProductCompanies_islender] DEFAULT (''),

[assemblyname] [varchar](100) COLLATE Latin1_General_CI_AI NOT NULL
CONSTRAINT [DF_ProductCompanies_assemblyname] DEFAULT ('AppOne.SOA'),

[classname] [varchar](100) COLLATE Latin1_General_CI_AI NOT NULL CONSTRAINT
[DF_ProductCompanies_classname] DEFAULT
('AppOne.SOA.Providers.ProductProvider'),

CONSTRAINT [PK_ProductCompanies] PRIMARY KEY CLUSTERED

(

[companyid] ASC

)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF, FILLFACTOR = 90) ON [PRIMARY]

) ON [PRIMARY]

GO

SET ANSI_PADDING OFF



USE [WS_Products]

GO

/****** Object: User [appone_soa] Script Date: 06/27/2006 00:56:54 ******/

GO

CREATE USER [appone_soa] FOR LOGIN [appone_soa] WITH DEFAULT_SCHEMA=[appone]



USE [WS_Products]

GO

/****** Object: DatabaseRole [db_appone] Script Date: 06/27/2006 00:57:11
******/

CREATE ROLE [db_appone] AUTHORIZATION [dbo]

USE [WS_Products]

GO

/****** Object: Schema [appone] Script Date: 06/27/2006 00:57:40 ******/

CREATE SCHEMA [appone] AUTHORIZATION [dbo]



USE [WS_Products]

GO

/****** Object: StoredProcedure [appone].[get_list] Script Date: 06/27/2006
01:00:18 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

create procedure [appone].[get_list] AS

select * from dbo.productcompanies



User "appone_soa" is a member of role "db_appone".

I have granted role "db_appone" execute permission on "appone" schema.

When I try to execute "appone.get_list" it fails with the following error:-

Msg 229, Level 14, State 5, Line 1

SELECT permission denied on object 'ProductCompanies', database
'WS_Products', schema 'dbo'.


What could I be doing wrong?


"Wei Lu [MSFT]" <weilu@xxxxxxxxxxxxxxxxxxxx> wrote in message
news:nrSjTeQmGHA.132@xxxxxxxxxxxxxxxxxxxxxxxx
Hi Param,

As I have mentioned, you could give a user only Execute permissions on a
stored procedure and the stored procedure could perform operations fine.

Here is my steps:

1. Create a table (tbl_Test ) in the database.

2. Create a Schema named mySchema in the database.

3. Create a database role named db_web.

4. Create a user named webUser and add it to the db_web role. The default
schema of this user is setting to mySchema.

5. Create a stored procedure named myShcema.doSelect.

The content of this proc is:

BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
select * from dbo.tbl_test
END

6. Grant the execute permission of myshcema to db_web role.

7. Login the server with user webUser.

8. Try to execute the stored procedure, it runs successfully and return
the
right result.

9. If try to select the result from the tbl_test directly, it will get the
following error:

SELECT permission denied on object 'tbl_Test', database 'Test', schema
'dbo'.

I think this is the behavior you wanted. Please try these steps on your
side and let me know the result. If you have anything unclear, please feel
free to let me know.

Also, you could post the scripts you use here for my troubleshotting.
Thank
you!

Sincerely,

Wei Lu
Microsoft Online Community Support

==================================================

When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.

==================================================
This posting is provided "AS IS" with no warranties, and confers no
rights.



.

Relevant Pages

  • Re: Using xp_smtp_sendmail from a stored proc
    ... the user database needs to be owned by 'sa' so that the ownership ... >>> application that the user needs execute permissions on the extended ... >>>>> legacy) for the extended stored procedure. ...
    (microsoft.public.sqlserver.security)
  • A philosophical question about inserts
    ... Is it better to let each developer create their own prepared SQL statements for inserts or to hide the schema from them and have them call stored procedures or views to get the data into the database? ... In the 2nd case, the schema is invisible to him/her but if a column was added to 'table', they would have to change their stored procedure call all the same. ...
    (comp.databases.ms-sqlserver)
  • Re: sql2k5 security
    ... you could give a user only Execute permissions on a ... stored procedure and the stored procedure could perform operations fine. ... Create a Schema named mySchema in the database. ...
    (microsoft.public.sqlserver.security)
  • Re: BizTalk 2004 SQL Adapter
    ... An incoming document is mapped to database stored procedures in the ... Because it has a header ... write the header and the detail is mapped to stored procedure to store ... But this doesn't look anything like the schema generated for a send ...
    (microsoft.public.biztalk.general)
  • Re: C# database access
    ... I do close the database and reader. ... performance of your stored procedure, it will help in managing the ... state of the database connection and allow you to have multiple ... I will look into the schema. ...
    (microsoft.public.dotnet.languages.csharp)