Tuesday, March 27, 2012

Granter does not have GRANT permission

As a contract database application developer I created a SQL database 5 year
s
ago for a single PC use application. We recently upgraded to a multi-user
environment and I handed the database to the IT department which installed i
t
on one of their Servers. 3 user group levels were created, and I assigned
rights to all the objects according to the user-group needs. Everything
worked fine for all the users until I started adding new objects and assigne
d
rights to those objects. When users (that are not part of the group that has
dbo rights) now try to access any newly created object they get this message
:
"Granter does not have GRANT permission". The IT department SQL manager is
clueless and useless. I'm clueless too, but I need to find the solution. Any
and all suggestions are very much appreciated.
Thank you,
polarbearThat means the user running the GRANT command doesn't have permissions to
GRANT permissions on the object in question. If I remember right, the
GRANTER must be an administrator or have been granted right from the WITH
GRANT OPTION of the GRANT statement.
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"polabear" <polabear@.spitzbergen.com> wrote in message
news:1E70AF39-93F5-49AC-93F5-198AB824B395@.microsoft.com...
> As a contract database application developer I created a SQL database 5
> years
> ago for a single PC use application. We recently upgraded to a multi-user
> environment and I handed the database to the IT department which installed
> it
> on one of their Servers. 3 user group levels were created, and I assigned
> rights to all the objects according to the user-group needs. Everything
> worked fine for all the users until I started adding new objects and
> assigned
> rights to those objects. When users (that are not part of the group that
> has
> dbo rights) now try to access any newly created object they get this
> message:
> "Granter does not have GRANT permission". The IT department SQL manager is
> clueless and useless. I'm clueless too, but I need to find the solution.
> Any
> and all suggestions are very much appreciated.
> Thank you,
> polarbear|||Roger,
Thanks for the reply. I've been told by the IT person that my login is part
of the "System Adminstrators", so it sounds like somebody needs to add the
"WITH GRANT OPTION" to my login.
Thanks,
polarbear
"Roger Wolter[MSFT]" wrote:

> That means the user running the GRANT command doesn't have permissions to
> GRANT permissions on the object in question. If I remember right, the
> GRANTER must be an administrator or have been granted right from the WITH
> GRANT OPTION of the GRANT statement.
> --
> This posting is provided "AS IS" with no warranties, and confers no rights
.
> Use of included script samples are subject to the terms specified at
> http://www.microsoft.com/info/cpyright.htm
> "polabear" <polabear@.spitzbergen.com> wrote in message
> news:1E70AF39-93F5-49AC-93F5-198AB824B395@.microsoft.com...
>
>|||Hi
No, If you are a member of sysadmin server role you don't need to add "WITH
GRANT OPTION of the GRANT statement"
"polabear" <polabear@.spitzbergen.com> wrote in message
news:C3F47EDB-FE22-45EE-B190-A3F17A39C678@.microsoft.com...[vbcol=seagreen]
> Roger,
> Thanks for the reply. I've been told by the IT person that my login is
> part
> of the "System Adminstrators", so it sounds like somebody needs to add the
> "WITH GRANT OPTION" to my login.
> Thanks,
> polarbear
>
> "Roger Wolter[MSFT]" wrote:
>|||> When users (that are not part of the group that has
> dbo rights) now try to access any newly created object they get this
> message:
> "Granter does not have GRANT permission".
It seems your application is doing a bit more than simply accessing objects.
This message implies that users are attempting to GRANT permissions via your
application. In that case, your users need the WITH GRANT. Without that
permission, in order to grant permissions the user must either own the
object, be a db_owner role member, db_securityadmin role member, the
database owner or sysadmin role member.
Happy Holidays
Dan Guzman
SQL Server MVP
"polabear" <polabear@.spitzbergen.com> wrote in message
news:1E70AF39-93F5-49AC-93F5-198AB824B395@.microsoft.com...
> As a contract database application developer I created a SQL database 5
> years
> ago for a single PC use application. We recently upgraded to a multi-user
> environment and I handed the database to the IT department which installed
> it
> on one of their Servers. 3 user group levels were created, and I assigned
> rights to all the objects according to the user-group needs. Everything
> worked fine for all the users until I started adding new objects and
> assigned
> rights to those objects. When users (that are not part of the group that
> has
> dbo rights) now try to access any newly created object they get this
> message:
> "Granter does not have GRANT permission". The IT department SQL manager is
> clueless and useless. I'm clueless too, but I need to find the solution.
> Any
> and all suggestions are very much appreciated.
> Thank you,
> polarbear|||Im not much of an expert yet but it sounds to me that you are dealing with a
broken ownership chain on a view. From my understanding, this is messy to
resolve. The owner of each object n a view has to give permissions to their
view. The way you avoid these problems is when you are actually creating the
view, by making the DBO owner of everything, Instead of just saying create
view (name) AS you say create view dbo.(name) AS. Too late for this kind of
help I guess but at least if you are working with a view you have an idea of
what may be causing this.
Message posted via http://www.droptable.com

No comments:

Post a Comment