permissions issue?

I am having a problem running this statement:
GRANT VIEW DEFINITION ON OBJECT::MyDatabase.MyStoredProcedure TO MyUser
I get this error:
Msg 15151, Level 16, State 1, Line 1
Cannot find the object 'MyStoredProcedure', because it does not exist
or you do not have permission.
MyStoredProcedure definitely DOES exist in the database, and I'm
running the statement as a sysadmin user. I even tried "sa" just in
case. But no dice. This seems like a very straightforward matter.
If I use the interface, not command line, I am able to grant
permissions easily. But there's something it doesn't like about my
above statement. Any ideas appreciated!
Thanks> If I use the interface, not command line, I am able to grant
> permissions easily.
Script the TSQL command submitted by the GUI and you will see the difference. Do you really have a
schema named MyDatabase in your database?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<tootsuite@.gmail.com> wrote in message news:1158610855.925197.188970@.i42g2000cwa.googlegroups.com...
>I am having a problem running this statement:
> GRANT VIEW DEFINITION ON OBJECT::MyDatabase.MyStoredProcedure TO MyUser
> I get this error:
> Msg 15151, Level 16, State 1, Line 1
> Cannot find the object 'MyStoredProcedure', because it does not exist
> or you do not have permission.
> MyStoredProcedure definitely DOES exist in the database, and I'm
> running the statement as a sysadmin user. I even tried "sa" just in
> case. But no dice. This seems like a very straightforward matter.
> If I use the interface, not command line, I am able to grant
> permissions easily. But there's something it doesn't like about my
> above statement. Any ideas appreciated!
> Thanks
>|||Here's the syntax, from Books Online:
GRANT <permission> ON
[ OBJECT :: ][ schema_name ]. object_name TO <database_principal>
So in your example:
schema_name = MyDatabase
object_name = MyStoredProcedure
database_principal = MyUser
Note that the name of the database should not be included, only the schema
name within the database.
--
HTH
Kalen Delaney, SQL Server MVP
<tootsuite@.gmail.com> wrote in message
news:1158610855.925197.188970@.i42g2000cwa.googlegroups.com...
>I am having a problem running this statement:
> GRANT VIEW DEFINITION ON OBJECT::MyDatabase.MyStoredProcedure TO MyUser
> I get this error:
> Msg 15151, Level 16, State 1, Line 1
> Cannot find the object 'MyStoredProcedure', because it does not exist
> or you do not have permission.
> MyStoredProcedure definitely DOES exist in the database, and I'm
> running the statement as a sysadmin user. I even tried "sa" just in
> case. But no dice. This seems like a very straightforward matter.
> If I use the interface, not command line, I am able to grant
> permissions easily. But there's something it doesn't like about my
> above statement. Any ideas appreciated!
> Thanks
>|||> Script the TSQL command submitted by the GUI and you will see the difference. Do you really have a
> schema named MyDatabase in your database?
No, of course not. But I don't like posting private company information
on the net :-)
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> <tootsuite@.gmail.com> wrote in message news:1158610855.925197.188970@.i42g2000cwa.googlegroups.com...
> >I am having a problem running this statement:
> >
> > GRANT VIEW DEFINITION ON OBJECT::MyDatabase.MyStoredProcedure TO MyUser
> >
> > I get this error:
> >
> > Msg 15151, Level 16, State 1, Line 1
> > Cannot find the object 'MyStoredProcedure', because it does not exist
> > or you do not have permission.
> >
> > MyStoredProcedure definitely DOES exist in the database, and I'm
> > running the statement as a sysadmin user. I even tried "sa" just in
> > case. But no dice. This seems like a very straightforward matter.
> >
> > If I use the interface, not command line, I am able to grant
> > permissions easily. But there's something it doesn't like about my
> > above statement. Any ideas appreciated!
> >
> > Thanks
> >|||Removing the db name worked, thanks
Kalen Delaney wrote:
> Here's the syntax, from Books Online:
> GRANT <permission> ON
> [ OBJECT :: ][ schema_name ]. object_name TO <database_principal>
> So in your example:
> schema_name = MyDatabase
> object_name = MyStoredProcedure
> database_principal = MyUser
> Note that the name of the database should not be included, only the schema
> name within the database.
> --
> HTH
> Kalen Delaney, SQL Server MVP
>
> <tootsuite@.gmail.com> wrote in message
> news:1158610855.925197.188970@.i42g2000cwa.googlegroups.com...
> >I am having a problem running this statement:
> >
> > GRANT VIEW DEFINITION ON OBJECT::MyDatabase.MyStoredProcedure TO MyUser
> >
> > I get this error:
> >
> > Msg 15151, Level 16, State 1, Line 1
> > Cannot find the object 'MyStoredProcedure', because it does not exist
> > or you do not have permission.
> >
> > MyStoredProcedure definitely DOES exist in the database, and I'm
> > running the statement as a sysadmin user. I even tried "sa" just in
> > case. But no dice. This seems like a very straightforward matter.
> >
> > If I use the interface, not command line, I am able to grant
> > permissions easily. But there's something it doesn't like about my
> > above statement. Any ideas appreciated!
> >
> > Thanks
> >|||I think Tibor was actually asking the same question I was. Is whatever you
used instead of MyDatabase a database name or a schema name?
Our guess is that you were confusing the two. If you had used the schema
name, it would have worked.
--
HTH
Kalen Delaney, SQL Server MVP
<tootsuite@.gmail.com> wrote in message
news:1158614641.882490.40330@.m7g2000cwm.googlegroups.com...
>> Script the TSQL command submitted by the GUI and you will see the
>> difference. Do you really have a
>> schema named MyDatabase in your database?
> No, of course not. But I don't like posting private company information
> on the net :-)
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> <tootsuite@.gmail.com> wrote in message
>> news:1158610855.925197.188970@.i42g2000cwa.googlegroups.com...
>> >I am having a problem running this statement:
>> >
>> > GRANT VIEW DEFINITION ON OBJECT::MyDatabase.MyStoredProcedure TO MyUser
>> >
>> > I get this error:
>> >
>> > Msg 15151, Level 16, State 1, Line 1
>> > Cannot find the object 'MyStoredProcedure', because it does not exist
>> > or you do not have permission.
>> >
>> > MyStoredProcedure definitely DOES exist in the database, and I'm
>> > running the statement as a sysadmin user. I even tried "sa" just in
>> > case. But no dice. This seems like a very straightforward matter.
>> >
>> > If I use the interface, not command line, I am able to grant
>> > permissions easily. But there's something it doesn't like about my
>> > above statement. Any ideas appreciated!
>> >
>> > Thanks
>> >
>|||>I think Tibor was actually asking the same question I was.
Indeed. Thanks for clarifying Kalen. :-)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:umdKy632GHA.3464@.TK2MSFTNGP03.phx.gbl...
>I think Tibor was actually asking the same question I was. Is whatever you used instead of
>MyDatabase a database name or a schema name?
> Our guess is that you were confusing the two. If you had used the schema name, it would have
> worked.
> --
> HTH
> Kalen Delaney, SQL Server MVP
>
> <tootsuite@.gmail.com> wrote in message news:1158614641.882490.40330@.m7g2000cwm.googlegroups.com...
>>
>> Script the TSQL command submitted by the GUI and you will see the difference. Do you really have
>> a
>> schema named MyDatabase in your database?
>> No, of course not. But I don't like posting private company information
>> on the net :-)
>>
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> <tootsuite@.gmail.com> wrote in message
>> news:1158610855.925197.188970@.i42g2000cwa.googlegroups.com...
>> >I am having a problem running this statement:
>> >
>> > GRANT VIEW DEFINITION ON OBJECT::MyDatabase.MyStoredProcedure TO MyUser
>> >
>> > I get this error:
>> >
>> > Msg 15151, Level 16, State 1, Line 1
>> > Cannot find the object 'MyStoredProcedure', because it does not exist
>> > or you do not have permission.
>> >
>> > MyStoredProcedure definitely DOES exist in the database, and I'm
>> > running the statement as a sysadmin user. I even tried "sa" just in
>> > case. But no dice. This seems like a very straightforward matter.
>> >
>> > If I use the interface, not command line, I am able to grant
>> > permissions easily. But there's something it doesn't like about my
>> > above statement. Any ideas appreciated!
>> >
>> > Thanks
>> >
>|||And it seems from his answer to me that his "MyDatabase" was a database
name, not a schema name.
As trainers, I can see we have our work cut out for us getting people to
understand this new concept!
--
Kalen Delaney, SQL Server MVP
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OTyVtdA3GHA.1252@.TK2MSFTNGP04.phx.gbl...
> >I think Tibor was actually asking the same question I was.
> Indeed. Thanks for clarifying Kalen. :-)
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> news:umdKy632GHA.3464@.TK2MSFTNGP03.phx.gbl...
>>I think Tibor was actually asking the same question I was. Is whatever you
>>used instead of MyDatabase a database name or a schema name?
>> Our guess is that you were confusing the two. If you had used the schema
>> name, it would have worked.
>> --
>> HTH
>> Kalen Delaney, SQL Server MVP
>>
>> <tootsuite@.gmail.com> wrote in message
>> news:1158614641.882490.40330@.m7g2000cwm.googlegroups.com...
>>
>> Script the TSQL command submitted by the GUI and you will see the
>> difference. Do you really have a
>> schema named MyDatabase in your database?
>> No, of course not. But I don't like posting private company information
>> on the net :-)
>>
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> <tootsuite@.gmail.com> wrote in message
>> news:1158610855.925197.188970@.i42g2000cwa.googlegroups.com...
>> >I am having a problem running this statement:
>> >
>> > GRANT VIEW DEFINITION ON OBJECT::MyDatabase.MyStoredProcedure TO
>> > MyUser
>> >
>> > I get this error:
>> >
>> > Msg 15151, Level 16, State 1, Line 1
>> > Cannot find the object 'MyStoredProcedure', because it does not exist
>> > or you do not have permission.
>> >
>> > MyStoredProcedure definitely DOES exist in the database, and I'm
>> > running the statement as a sysadmin user. I even tried "sa" just in
>> > case. But no dice. This seems like a very straightforward matter.
>> >
>> > If I use the interface, not command line, I am able to grant
>> > permissions easily. But there's something it doesn't like about my
>> > above statement. Any ideas appreciated!
>> >
>> > Thanks
>> >
>>
>|||On Tue, 19 Sep 2006 09:55:10 -0700, "Kalen Delaney"
<replies@.public_newsgroups.com> wrote:
>As trainers, I can see we have our work cut out for us getting people to
>understand this new concept!
Think of it as job security. 8-)
Roy