exec proc premission

Hi
I created a user called development in sql server 2005 and make it a member
of datareader,datewriter and ddladmin role. now I created a proc under that
development account. however when I try to exec that proc, its give a
permission denied error. How come? it was able to create proc, but not
exec, its there something I have to do?
thanksPermission denied on what object? Does the proc manipulate some table,
view or proc that the development user does not have permissions to
manipulate? Are there explicit deny permissions for the development
user on some objects in the DB? Can you post actual code and actual
error messages?
*mike hodgson*
http://sqlnerd.blogspot.com
paulixml wrote:

>Hi
>I created a user called development in sql server 2005 and make it a member
>of datareader,datewriter and ddladmin role. now I created a proc under tha
t
>development account. however when I try to exec that proc, its give a
>permission denied error. How come? it was able to create proc, but not
>exec, its there something I have to do?
>thanks
>
>|||hi
here what I did, I create the prc connected as development,
create proc [dbo].[getpivot1_prc]as
select * from pivot_tmp
then I try to exec it after I create, I get this
Msg 229, Level 14, State 5, Procedure getpivot1_prc, Line 1
EXECUTE permission denied on object 'getpivot1_prc', database 'cap2005',
schema 'dbo'.
thanks
"Mike Hodgson" <e1minst3r@.gmail.com> wrote in message
news:uRc3wnoaGHA.1200@.TK2MSFTNGP03.phx.gbl...
> Permission denied on what object? Does the proc manipulate some table,
> view or proc that the development user does not have permissions to
> manipulate? Are there explicit deny permissions for the development
> user on some objects in the DB? Can you post actual code and actual
> error messages?
> --
> *mike hodgson*
> http://sqlnerd.blogspot.com
>
> paulixml wrote:
>
>|||The db_ddladmin role allowed you to create the proc as a different user
(dbo) but since you explicitly stated the owner of that proc when you
created it, the development user is not the owner and therefore doesn't
have permissions to execute it. And being a memory of db_datareader,
db_datawriter & db_ddladmin does not imply execute permissions on that
new proc.
Either create the proc as development.getpivot1_prc (CREATE PROC
development.getpivot1_prc AS ...), which would make the development user
the owner of the proc and therefore imply execute permissions for the
development user, or explicitly grant execute permissions on that proc
to the development user or a role that that user is a member of (but you
can't change the permissions with the development user because
db_ddladmin does not allow you to run GRANT statements).
Why are you using the development user to create a proc owned by the dbo
user?
*mike hodgson*
http://sqlnerd.blogspot.com
paulixml wrote:

>hi
>here what I did, I create the prc connected as development,
>create proc [dbo].[getpivot1_prc]as
>select * from pivot_tmp
>then I try to exec it after I create, I get this
>Msg 229, Level 14, State 5, Procedure getpivot1_prc, Line 1
>EXECUTE permission denied on object 'getpivot1_prc', database 'cap2005',
>schema 'dbo'.
>thanks
>"Mike Hodgson" <e1minst3r@.gmail.com> wrote in message
>news:uRc3wnoaGHA.1200@.TK2MSFTNGP03.phx.gbl...
>
>
>

Exclusive Join

I have a link table between a user table and a lookup that contains the following data

ID Role
-- --
92166 1
92166 11
92167 7
92167 11
92210 3
92210 7
92210 11
92211 7
92211 11
92212 7
92212 11
92213 7
92213 11

92213 3

The Link table stores whether a user has a combination of rolea. I need to be able to ask for any given user IE 92210 has the roles 3 and 7 any additional roles are ok but they must have all of the combinations I am asking for. The role combinations can be anywhere from 1 to 40. So in one case i have to ask for roles (3,7) and in another (1,7,11) The only way I can figure out how to do this is a dynamic self join or by using a cursor (yuck).

This is a legacy application and the requirement changed from roles being inclusive to exclusive and a table structure change is out of the question.

There must be a better way. Any help would be appreciated.

Thanks in advance

You could do something like joining to the passed array with using a split function which is wrote sometime ago:

CREATE FUNCTION dbo.Split
(
@.String VARCHAR(200),
@.Delimiter VARCHAR(5)
)
RETURNS @.SplittedValues TABLE
(
OccurenceId SMALLINT IDENTITY(1,1),
SplitValue VARCHAR(200)
)
AS
BEGIN
DECLARE @.SplitLength INT

WHILE LEN(@.String) > 0
BEGIN
SELECT @.SplitLength = (CASE CHARINDEX(@.Delimiter,@.String) WHEN 0 THEN
LEN(@.String) ELSE CHARINDEX(@.Delimiter,@.String) -1 END)

INSERT INTO @.SplittedValues
SELECT SUBSTRING(@.String,1,@.SplitLength)

SELECT @.String = (CASE (LEN(@.String) - @.SplitLength) WHEN 0 THEN ''
ELSE RIGHT(@.String, LEN(@.String) - @.SplitLength - 1) END)
END
RETURN
END

This evaluates to:

DELCARE @.SomeValues VARCHAR(100)
SET @.SomeValues = '1,7,11'

SELECT 'Access Granted'
FROM SomeTable ST
INNER JOIN dbo.Split(@.SomeValues,',') S ON
ON S.SplitValue = ST.Role
WHERE ID = 95321
HAVING COUNT(*) = (SELECT COUNT(*) FROM dbo.Split(SomeValues,','))

This is untested and maybe need to be modified, try to use that in the case you don′t wanna use dynamic sql.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||

Thanks, that put me on the right track and now have a udf that does the job

sql