Permissions grants, denies don't take effect

Hi,

I'm trying to grant/deny object permissions in a user database using Enterprise Manager and query tool without success (for ‘public’ role and individual sql logins). I’m not getting any error messages. Permission changes just don’t take effect. Although, there are few objects, which already have permissions granted and I'm able to change permissions for these ones.

How can I change the permissions?

you might have a granted permission on a GUEST account

or an NT domain group. please check.

thats why denying wont work

|||there is no permissions granted or denied for the 'guest' account. I don't use domain groups.|||

What are the actual denies that you are trying to set. Did you verify that they appear in the system catalog (server_permissions, database_permissions)? Have you tried applying the denies using TSQL: DENY?

Thanks
Laurentiu

permissions

I am working http location and using sql server 2005 ,it is showing an error as " DELETE permission denied on object 'CourseDetails', database 'LOGIN', schema 'dbo'." CourseDetails is my table name and LOGIN is my database name.

Goto SQL Server Management Studio 2005--> Goto your database (LOGIN)--> Right click on the table name (CourseDetails)--> Select Properties

There goto the Permissions tab and click add... You can add the permission here...

But the question is WHY would you want to add delete permissions on your SQL Table? What you should do is write a Stored Procedure to delete the data in there and give the procedure execute permissions.

|||

Thank u deepak

Permission Denied to object

We recently brought up SQL Server and migrated our Access 2000 back end DB t
o
it. The SQL Server security seemed pretty straight forward to me...I set up
the users and the roles and assigned the roles rights to my tables. The
problem is that my users get a Permission Denied error when ever the Access
2000 front end tries to access any SQL Server table unless I add them to the
System Administrator server role. I've gone over everything and I can't see
m
to figure this out. The only thing I can find that seems strange is that on
that database, my login name is associated with the "dbo" name. I think tha
t
makes me the owner of the db and I'm wondering if that is what is causing my
issue. I'd greatly appreciate any insight on how to get this issue taken
care of.
Thanks in advance!
JimNevermind, I found the answer to my problem in another post. Don't assign
the db_denydatareader or db_denydatawriter role to the user. That cleared u
p
my problem.
"Jim" wrote:

> We recently brought up SQL Server and migrated our Access 2000 back end DB
to
> it. The SQL Server security seemed pretty straight forward to me...I set
up
> the users and the roles and assigned the roles rights to my tables. The
> problem is that my users get a Permission Denied error when ever the Acces
s
> 2000 front end tries to access any SQL Server table unless I add them to t
he
> System Administrator server role. I've gone over everything and I can't s
eem
> to figure this out. The only thing I can find that seems strange is that
on
> that database, my login name is associated with the "dbo" name. I think t
hat
> makes me the owner of the db and I'm wondering if that is what is causing
my
> issue. I'd greatly appreciate any insight on how to get this issue taken
> care of.
> Thanks in advance!
> Jim

permission denied on object 'sp_sdidebug', database 'dbname',

Hi.
I have seen lots of discussion.
My starting problem was that after installing sql server 2000 and sp3, I cou
ld
not debug stored procedures.
I thought I saw the solution in MSDN, but
it did not work for me (I could not grant execute
on sp_sdidebug to my user).
short end is.. I had to do something else.
I'm developing on a local mssqlserver 2000 sp3
windows 2000 is the o/s
I logged in as the sp and ran this:
EXECUTE sp_sdidebug 'LEGACY_ON'
Then I tried to enter this:
GRANT EXECUTE
ON sp_sdidebug
TO MYUSER
But I got an error about not being able to set permissions for objects owned
by others.
I'm obviously clueless, but I did find a blurb from someone who said they
had to add the users to the MASTER database as users with db_owner checked.
After I did this, I could debug sp's, but still could not do the 'GRANT
EXECUTE ON sp_sdidebug'.
So
1 - what is the best way to get the (EXECUTE sp_sdidebug 'LEGACY_ON')
executed each time the db starts up? (if I need to put it in some sp, how
exactly do I get that called at startup?)
2 - any idea what I'm boffing here to prevent the 'GRANT EXECUTE ON
sp_sdidebug' from working?
3 - any thoughts about the MASTER.db_owner "solution"?
Thanks for your patience as I am sure this is a well trampled ground.
Jeff KishJeff,
Why do you need to GRANT execute on this sp?
AMB
"Jeff Kish" wrote:

> Hi.
> I have seen lots of discussion.
> My starting problem was that after installing sql server 2000 and sp3, I c
ould
> not debug stored procedures.
> I thought I saw the solution in MSDN, but
> it did not work for me (I could not grant execute
> on sp_sdidebug to my user).
> short end is.. I had to do something else.
> I'm developing on a local mssqlserver 2000 sp3
> Windows 2000 is the o/s
> I logged in as the sp and ran this:
> EXECUTE sp_sdidebug 'LEGACY_ON'
> Then I tried to enter this:
> GRANT EXECUTE
> ON sp_sdidebug
> TO MYUSER
> But I got an error about not being able to set permissions for objects own
ed
> by others.
> I'm obviously clueless, but I did find a blurb from someone who said they
> had to add the users to the MASTER database as users with db_owner checked
.
> After I did this, I could debug sp's, but still could not do the 'GRANT
> EXECUTE ON sp_sdidebug'.
>
> So
> 1 - what is the best way to get the (EXECUTE sp_sdidebug 'LEGACY_ON')
> executed each time the db starts up? (if I need to put it in some sp, how
> exactly do I get that called at startup?)
> 2 - any idea what I'm boffing here to prevent the 'GRANT EXECUTE ON
> sp_sdidebug' from working?
> 3 - any thoughts about the MASTER.db_owner "solution"?
> Thanks for your patience as I am sure this is a well trampled ground.
> Jeff Kish
>|||When I installed ms sql server with sp3 I found I could not run the debug
on stored procedures. It was grayed out.
Once I did some research, I determined (I "think" from MSDN) that
I needed to GRANT to run the debugger logged in as this user.
I got an error doing the GRANT. Looking around a bit more, I saw that
I also needed to run in legacy mode for this object.
I submitted a script for that, but I still could not GRANT.
After substantially more investigation, I saw one user was able to get the
debugger running if he added his user as a db_owner to the master
database.
After I did this, I still could not do the GRANT, but was able to run the
debugger on stored procedures.
Now I'm trying to figure out if what I did was a reasonable solution, and
why I could not do the GRANT. Finally, I'm casting about for an explanation
and some level of confidence for what I did to "fix" the problem.
Also, I wanted to figure out how to run the LEGACY script at startup so I
don't need to do it every time.
Thanks
On Mon, 28 Feb 2005 16:49:04 -0800, "Alejandro Mesa"
<AlejandroMesa@.discussions.microsoft.com> wrote:
>Jeff,
>Why do you need to GRANT execute on this sp?
>
>AMB
>
>"Jeff Kish" wrote:
>
Jeff Kish

permission denied on object

I connect in my database "test" with the user "teste" by SQL QueryAnalyzer (localhost;DatabaseName=test;SelectMethod=cursor),
but when i run the SQL (select * from user) return the error
"SELECT permission denied on object 'user', database 'test', owner 'teste'.

I already put all permission to the user "user".

My database test has a table "user". The owners "teste" and the Types "User"
The user "teste" has all permission to the table "user" (select, update, insert ...)

what i need to do ? Whats wrong ?The user may possibly be denied access to the table or a member of a group that is denied access to the table or possibly a member of db_denydatareader.

permission denied

iam working with http location and using sql server 2005its getting an error as "SELECT permission denied on object UserDetails' database 'elearning', schema 'dbo'.""UserDetails" is my table name"elearning" is database namei worked same project with filesystem location ,there it is workingThis is obviously a permissions issue. You need to ensure that the user your connected to the database with has select permissions on at least the "UserDetails" table.|||

It is a issue retaled to lack of permission for the user account you are using to connect.

To resolve this issue in SQL management Studio right click the username which is placed under Security => Login.

A modal window will be opened and in User Mapping section setect the desired DB and check db_datareader for that user. Click ok. Now you are done.

Let me know if need any further clarifications

permission denied

iam working with http location and using sql server 2005 its getting an error as "INSERT permission denied on object CourseDetails, database 'mydb', schema 'dbo'." "CourseDetails" is my table name "mydb" is database name i worked same project with filesystem location ,there it is working

This is almost certainly being caused because the account that is running the web site (ASPNET or NETWORK SERVICE, depending on which version of IIS you're running) is not allowed permission to access the SQL Server database. Note that for a file based application, running under the development web server, your user account will have been used.

Check the connection string that is being used in the web site. If it is using integrated security (likely), then you will need to ensure that the ASPNET or NETWORK SERVICE account (or, more precisely, the account that is being used to run the web site) is allowed access to the database.