I'm a newbie to the admin side of SqlServer. I created a new login:
<code>
CREATE LOGIN pmd_app
WITH PASSWORD='********'
</code>
I then used the "Server Management Studio Express" to create a new user in
my DB with the same name, then give the logical permissions, at least
logical to me. I can read and write table data with this new user, but I'm
getting EXECUTE permission errors when calling sprocs. I know how to grant
permissions to a user on a per object basis, but what role memberships
should I be using to give them EXECUTE permissions to all new sprocs that I
create?
I'm looking over BOL to see if I can find the answer, but so far not coming
up with anything.
Also, if anyone knows a good place to find an article covering SQLServer
security, role, permission, schemas, etc that would be awesome ;)
Thanks for any help,
Steve> getting EXECUTE permission errors when calling sprocs. I know how to
> grant permissions to a user on a per object basis, but what role
> memberships
If the user is an owner of the object he/she has an EXECUTE permissions
automatically.
Who is the owner of the object?
"sklett" <sklett@.mddirect.com> wrote in message
news:ePmSgM1NGHA.3732@.TK2MSFTNGP10.phx.gbl...
> I'm a newbie to the admin side of SqlServer. I created a new login:
> <code>
> CREATE LOGIN pmd_app
> WITH PASSWORD='********'
> </code>
>
> I then used the "Server Management Studio Express" to create a new user in
> my DB with the same name, then give the logical permissions, at least
> logical to me. I can read and write table data with this new user, but
> I'm getting EXECUTE permission errors when calling sprocs. I know how to
> grant permissions to a user on a per object basis, but what role
> memberships should I be using to give them EXECUTE permissions to all new
> sprocs that I create?
> I'm looking over BOL to see if I can find the answer, but so far not
> coming up with anything.
> Also, if anyone knows a good place to find an article covering SQLServer
> security, role, permission, schemas, etc that would be awesome ;)
> Thanks for any help,
> Steve
>|||That's a 2000 way of thinking. The new way is to associate everything via
schemas.
Create a schema, grant your users execute permissions in the schema, create
all you new procs under that schema...easy!
"sklett" wrote:
> I'm a newbie to the admin side of SqlServer. I created a new login:
> <code>
> CREATE LOGIN pmd_app
> WITH PASSWORD='********'
> </code>
>
> I then used the "Server Management Studio Express" to create a new user in
> my DB with the same name, then give the logical permissions, at least
> logical to me. I can read and write table data with this new user, but I'm
> getting EXECUTE permission errors when calling sprocs. I know how to grant
> permissions to a user on a per object basis, but what role memberships
> should I be using to give them EXECUTE permissions to all new sprocs that I
> create?
> I'm looking over BOL to see if I can find the answer, but so far not coming
> up with anything.
> Also, if anyone knows a good place to find an article covering SQLServer
> security, role, permission, schemas, etc that would be awesome ;)
> Thanks for any help,
> Steve
>
>|||oooh, uncharted territory! - scary and exciting :)
So it sounds like I need to put my tools down and read the manual. I will
do some Schema research and figure just how they work and what they do.
Thanks for the tip!
"mulhall" <mulhall@.discussions.microsoft.com> wrote in message
news:C6F5B46D-52D0-4EC2-9782-A72A53774A26@.microsoft.com...
> That's a 2000 way of thinking. The new way is to associate everything via
> schemas.
> Create a schema, grant your users execute permissions in the schema,
> create
> all you new procs under that schema...easy!
> "sklett" wrote:
>> I'm a newbie to the admin side of SqlServer. I created a new login:
>> <code>
>> CREATE LOGIN pmd_app
>> WITH PASSWORD='********'
>> </code>
>>
>> I then used the "Server Management Studio Express" to create a new user
>> in
>> my DB with the same name, then give the logical permissions, at least
>> logical to me. I can read and write table data with this new user, but
>> I'm
>> getting EXECUTE permission errors when calling sprocs. I know how to
>> grant
>> permissions to a user on a per object basis, but what role memberships
>> should I be using to give them EXECUTE permissions to all new sprocs that
>> I
>> create?
>> I'm looking over BOL to see if I can find the answer, but so far not
>> coming
>> up with anything.
>> Also, if anyone knows a good place to find an article covering SQLServer
>> security, role, permission, schemas, etc that would be awesome ;)
>> Thanks for any help,
>> Steve
>>|||"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:uBW9KT4NGHA.1460@.TK2MSFTNGP10.phx.gbl...
>> getting EXECUTE permission errors when calling sprocs. I know how to
>> grant permissions to a user on a per object basis, but what role
>> memberships
> If the user is an owner of the object he/she has an EXECUTE permissions
> automatically.
> Who is the owner of the object?
I don't know :)
if the full name of the object is any indicator ("dbo.usp_MySprocName") I
would have to guess 'dbo' - but I could be wrong. Schemas are brand new to
me, I don't know whay they are or how they work.
Looking at the already defined schemas in my DB, I don't see any obvious
ones that would indicate EXECUTE permissions, I may need to make my own?
Sounds like schemas are my solution, I need to learn about them. Thanks for
the post!
-Steve
>
> "sklett" <sklett@.mddirect.com> wrote in message
> news:ePmSgM1NGHA.3732@.TK2MSFTNGP10.phx.gbl...
>> I'm a newbie to the admin side of SqlServer. I created a new login:
>> <code>
>> CREATE LOGIN pmd_app
>> WITH PASSWORD='********'
>> </code>
>>
>> I then used the "Server Management Studio Express" to create a new user
>> in my DB with the same name, then give the logical permissions, at least
>> logical to me. I can read and write table data with this new user, but
>> I'm getting EXECUTE permission errors when calling sprocs. I know how to
>> grant permissions to a user on a per object basis, but what role
>> memberships should I be using to give them EXECUTE permissions to all new
>> sprocs that I create?
>> I'm looking over BOL to see if I can find the answer, but so far not
>> coming up with anything.
>> Also, if anyone knows a good place to find an article covering SQLServer
>> security, role, permission, schemas, etc that would be awesome ;)
>> Thanks for any help,
>> Steve
>
