Permission Denied Error: couple

Showing posts with label couple. Show all posts

Wednesday, March 21, 2012

permissions not scripting in 2000

We have several databases that were migrated to SQL2000 from 7.0 on the same
server running Windows 2000 Server. Only a couple of the databases are having
a problem that when we script out the stored procedures or any object for
that matter (with the script permissions box selected) the object or SP is
scripted out but the GRANT permissions portion of the script is omitted. We
also cannot see the permissions for the object in the GUI of Enterprise
Manager. The other databases work fine on the same server. Is it a switch for
this particular database or something? Any help on this matter would be
greatly appreciated.Did you check "Script object-level permissions" on the Options tab.
Also, you can generate scripts via Query Analyzer. It has the options for
scripting permissions, too.
-oj
"Dennis" <Dennis@.discussions.microsoft.com> wrote in message
news:675C1FC1-62D7-43DB-8E09-4FBC3DBA81F8@.microsoft.com...
> We have several databases that were migrated to SQL2000 from 7.0 on the
> same
> server running Windows 2000 Server. Only a couple of the databases are
> having
> a problem that when we script out the stored procedures or any object for
> that matter (with the script permissions box selected) the object or SP is
> scripted out but the GRANT permissions portion of the script is omitted.
> We
> also cannot see the permissions for the object in the GUI of Enterprise
> Manager. The other databases work fine on the same server. Is it a switch
> for
> this particular database or something? Any help on this matter would be
> greatly appreciated.|||First of all thanks for the response. Yes, the "Script object-level
permissions" box is checked. It's weird because it seems to work on the other
databases that are on the same instance of SQLServer but not on just this
one. The object level permissions do not show up if you pull up the object in
the EM GUI either. I have ben told that you can see the permissions in the
syspermissions table they just don't appear in the GUI or when scripted. Any
help is appreciated.
"oj" wrote:
> Did you check "Script object-level permissions" on the Options tab.
> Also, you can generate scripts via Query Analyzer. It has the options for
> scripting permissions, too.
>
> --
> -oj
>
> "Dennis" <Dennis@.discussions.microsoft.com> wrote in message
> news:675C1FC1-62D7-43DB-8E09-4FBC3DBA81F8@.microsoft.com...
> > We have several databases that were migrated to SQL2000 from 7.0 on the
> > same
> > server running Windows 2000 Server. Only a couple of the databases are
> > having
> > a problem that when we script out the stored procedures or any object for
> > that matter (with the script permissions box selected) the object or SP is
> > scripted out but the GRANT permissions portion of the script is omitted.
> > We
> > also cannot see the permissions for the object in the GUI of Enterprise
> > Manager. The other databases work fine on the same server. Is it a switch
> > for
> > this particular database or something? Any help on this matter would be
> > greatly appreciated.
>
>|||Sorry for the late reply...been out of town for the last few weeks.
Anyway, this sounds like a problem with the GUI (though, I'm unable to repro
it at my end). If this is important to you, you can open a case with MS
Support. They won't charge you if this is indeed a bug.
--
-oj
"Dennis" <Dennis@.discussions.microsoft.com> wrote in message
news:90927829-B83C-4A24-BDF0-E87B276914B8@.microsoft.com...
> First of all thanks for the response. Yes, the "Script object-level
> permissions" box is checked. It's weird because it seems to work on the
> other
> databases that are on the same instance of SQLServer but not on just this
> one. The object level permissions do not show up if you pull up the object
> in
> the EM GUI either. I have ben told that you can see the permissions in the
> syspermissions table they just don't appear in the GUI or when scripted.
> Any
> help is appreciated.
> "oj" wrote:
>> Did you check "Script object-level permissions" on the Options tab.
>> Also, you can generate scripts via Query Analyzer. It has the options for
>> scripting permissions, too.
>>
>> --
>> -oj
>>
>> "Dennis" <Dennis@.discussions.microsoft.com> wrote in message
>> news:675C1FC1-62D7-43DB-8E09-4FBC3DBA81F8@.microsoft.com...
>> > We have several databases that were migrated to SQL2000 from 7.0 on the
>> > same
>> > server running Windows 2000 Server. Only a couple of the databases are
>> > having
>> > a problem that when we script out the stored procedures or any object
>> > for
>> > that matter (with the script permissions box selected) the object or SP
>> > is
>> > scripted out but the GRANT permissions portion of the script is
>> > omitted.
>> > We
>> > also cannot see the permissions for the object in the GUI of Enterprise
>> > Manager. The other databases work fine on the same server. Is it a
>> > switch
>> > for
>> > this particular database or something? Any help on this matter would be
>> > greatly appreciated.
>>sql

Tuesday, March 20, 2012

Permissions in SQLExpress

It looks like you connect to the database as a member of PWNDAdmin and try to grant the role permissions you don't have. The error you were getting is caused by running as a low privileged principal with no access to the procedures. You don't need to grant db_owner status to grant execute permission, you just need to be able to grant permissions on the object and one way to achieve this would be to connect to the database as the dbo before granting the permissions.

Thanks
Laurentiu

|||Actually, I have my own user in the database for my login and am assigned db_owner. I ran a query that showed that I indeed had db_owner status. Would it perhaps help if I wasn't in the PWDNAdmin group on the network?
Thanks,
Gerry
|||

But didn't you get db_owner status because you're a PWDNAdmin member and you made that a db_owner? Or were you a db_owner before that?

As a db_owner, you should be able to grant any permission on objects in the database.

Can you post the T-SQL grant statement that fails as well as the text and number of the error that you are receiving?

Thanks
Laurentiu

|||I was a db_owner before giving that to PWDNAdmin. You have arrived at my delima!!! I should, as a db_owner grant permissions on objects in the database.
The T-SQL is:
GRANT EXECUTE ON mystoredprocedure TO PWDNAdmin
To get the error I will have to get back to the office and run the program. I will do that tomorrow.
Thanks for your help in deciphering my problem. Sure as tomorrow is the shortest day, my problem will be some simple thing, but until I find it....uuuuggghh.
Gerry
|||

Maybe the mystoredprocedure resides in a special schema and you should prefix its name with the schema name. The statement may fail because it cannot find the procedure within the schemas it searches by default.

Thanks
Laurentiu

|||The statement itself works. What doesn't work is the program when the procedure is called.
|||I am not sure about the schema that the query belongs to.
Maybe I need to review, for myself as well as for you to help me better. The database I am using was upgraded from MSDE so it brought along all the permissions for PWDNAdmin and the stored procedures. One of my programs that has been using the database for a few months works without any problems after the upgrade to SQLExpress.
Now I have created stored procedures in a separate database located on my notebook (so I can develope at my home office) that I have then created in the database in question.
Here is the T-SQL that I used to create the procedures (well only one 'cause there are lots)
STart:
USE [ProcWareDOTNET]
GO
/****** Object: StoredProcedure [dbo].[AcctInitialLogSelect] Script Date: 12/21/2005 10:52:05 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
Create PROCEDURE [dbo].[AcctInitialLogSelect]
AS
SET NOCOUNT ON;
SELECT InvoiceNumber, PONumber, DateReceived, VendorName, Value, Initials, ErrorDescription FROM ATInitialLog
Grant Execute on dbo.AcctInitialLogSelect to PWDNAdmin
END
Note the Start and End are so you know where the sql ends. Anywho. I have now discovered the problem. If I remove the Grant Excecute line then my problem goes away!!! Now why would that be? Very interesting.
|||

The grant ends up being part of the procedure definition, so when you will call the procedure, you will actually perform a grant on it. You should always use BEGIN and END to delimit the body of your stored procedure. Also, simply adding a GO before the grant would make the script run as intended, but I recommend always using BEGIN and END.

Thanks
Laurentiu

|||Thank you Laurentiu for your help is solving my problem. I am not trained in SQL and in fact all my programming knowledge is self taught. There are times when the scope of work requires venturing into new areas previously unknown. This is one of those cases. I have worked for years using Access databases and in the last year the scope of our work finally made us realize that it was time to lay Access to rest and get with the program and true SQL databases. As such, there are times when learning while doing means getting it done sooner and perhaps bulkier rather than getting it done with precision that only years of knowledge can bring.
Thanks again have great holiday season and terrific new year,
Gerry
|||

I am glad I could be of help. Happy holidays to you as well.

Thanks
Laurentiu

Friday, March 9, 2012

Permissions

Hello. I have a problem with assigning permissions in SQLExpress. First a
little background. A couple of weeks ago we upgraded from MSDE to
SQLExpress. The database came over fine, tables, stored procedures, roles
and since everything ran ok I am sure that it preserved all the permissions
from the roles. The database has two programs and two distinct groups of
users that create and edit data in the database. Program 1 is a complete
program, that is, it has been running for several months using the MSDE and
the program never skipped a beat when we switched to SQLExpress. Program 2
is in development and would be considered a beta version.
This is where I am having problems. For program 2, I have created more
stored procedures in the database. The users of both programs are in a role
called PWDNAdmin (actually they are all in the same group in the main
network). I have used an transact-sql statement to grant permission to
execute these procedures to the group (GRANT EXECUTE "mynewquery" to
'PWDNAdmin') within the SQL Server Management Express program. When I run
the program from a users computer it says that the query doesn't exist or
the user doesn't have permissions! So, to test to be sure that the query
is there I granted the role PWDNAdmin to be a db_owner. Sure enough it
works. Now the question is, how do I grant execute permission without
granting db_owner status?
Hope that is clear.
Thanks,
Gerry DyckHi,
add that users to db_datareader or db_ddladmin(it will grant permission to
Data Defination Languages)
:-)
Regards
--
Andy Davis
Activecrypt Team
---
SQL Server Encryption Software
http://www.activecrypt.com
"News" wrote:

> Hello. I have a problem with assigning permissions in SQLExpress. First
a
> little background. A couple of weeks ago we upgraded from MSDE to
> SQLExpress. The database came over fine, tables, stored procedures, roles
> and since everything ran ok I am sure that it preserved all the permission
s
> from the roles. The database has two programs and two distinct groups of
> users that create and edit data in the database. Program 1 is a complete
> program, that is, it has been running for several months using the MSDE an
d
> the program never skipped a beat when we switched to SQLExpress. Program
2
> is in development and would be considered a beta version.
> This is where I am having problems. For program 2, I have created more
> stored procedures in the database. The users of both programs are in a ro
le
> called PWDNAdmin (actually they are all in the same group in the main
> network). I have used an transact-sql statement to grant permission to
> execute these procedures to the group (GRANT EXECUTE "mynewquery" to
> 'PWDNAdmin') within the SQL Server Management Express program. When I run
> the program from a users computer it says that the query doesn't exist or
> the user doesn't have permissions! So, to test to be sure that the query
> is there I granted the role PWDNAdmin to be a db_owner. Sure enough it
> works. Now the question is, how do I grant execute permission without
> granting db_owner status?
> Hope that is clear.
> Thanks,
> Gerry Dyck
>
>|||Hi,
add them to db_ddladmin or grant db_datareader
Andy Davis
Activecrypt Team
---
SQL Server Encryption Software
http://www.activecrypt.com
"News" wrote:

> Hello. I have a problem with assigning permissions in SQLExpress. First
a
> little background. A couple of weeks ago we upgraded from MSDE to
> SQLExpress. The database came over fine, tables, stored procedures, roles
> and since everything ran ok I am sure that it preserved all the permission
s
> from the roles. The database has two programs and two distinct groups of
> users that create and edit data in the database. Program 1 is a complete
> program, that is, it has been running for several months using the MSDE an
d
> the program never skipped a beat when we switched to SQLExpress. Program
2
> is in development and would be considered a beta version.
> This is where I am having problems. For program 2, I have created more
> stored procedures in the database. The users of both programs are in a ro
le
> called PWDNAdmin (actually they are all in the same group in the main
> network). I have used an transact-sql statement to grant permission to
> execute these procedures to the group (GRANT EXECUTE "mynewquery" to
> 'PWDNAdmin') within the SQL Server Management Express program. When I run
> the program from a users computer it says that the query doesn't exist or
> the user doesn't have permissions! So, to test to be sure that the query
> is there I granted the role PWDNAdmin to be a db_owner. Sure enough it
> works. Now the question is, how do I grant execute permission without
> granting db_owner status?
> Hope that is clear.
> Thanks,
> Gerry Dyck
>
>

Permission Denied Error

Wednesday, March 21, 2012

permissions not scripting in 2000

permissions not scripting in 2000

permissions not scripting in 2000

Tuesday, March 20, 2012

Permissions in SQLExpress

Friday, March 9, 2012

Permissions

Followers

Blog Archive

About Me