|
Home > Archive > MS SQL Server > October 2006 > permissions issue?
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
permissions issue?
|
|
| tootsuite@gmail.com 2006-10-24, 6:29 pm |
| 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
| |
| Tibor Karaszi 2006-10-24, 6:29 pm |
| > 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
>
| |
| Kalen Delaney 2006-10-24, 6:29 pm |
| 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
>
| |
| tootsuite@gmail.com 2006-10-24, 6:29 pm |
|
>
> 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 :-)
[color=darkred]
>
> --
> 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...
| |
| tootsuite@gmail.com 2006-10-24, 6:29 pm |
| Removing the db name worked, thanks
Kalen Delaney wrote:[color=darkred
]
> 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...
| |
| Kalen Delaney 2006-10-24, 6:29 pm |
| 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...
>
>
> No, of course not. But I don't like posting private company information
> on the net :-)
>
>
| |
| Tibor Karaszi 2006-10-24, 6:29 pm |
| >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_newsg
roups.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...
>
>
| |
| Kalen Delaney 2006-10-24, 6:29 pm |
| 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@hotmai
l.nomail.com> wrote in
message news:OTyVtdA3GHA.1252@TK2MSFTNGP04.phx.gbl...
>
> 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_newsg
roups.com> wrote in message
> news:umdKy632GHA.3464@TK2MSFTNGP03.phx.gbl...
>
| |
| Roy Harvey 2006-10-24, 6:29 pm |
| On Tue, 19 Sep 2006 09:55:10 -0700, "Kalen Delaney"
< replies@public_newsg
roups.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
|
|
|
|
|