Permissions not Saving

I am having a strange problem with certain databases.
Some of the permissions I set for certain tables are not
saving. I right click on the table,move over "All tasks"
and select "Manage Permissions". I tick the permissions I
want and click ok. When I exit and go back in the
permissions I have set are gone.
Can anyone explain.Hi,
Not sure of the issue in Enterprise manager. Can you try giving the
permissions using GRANT statement from Query analyzer.
How to give prev.
GRANT SELECT on table_name to <user_name>
GRANT INSERT on table_name to <User_name>
GRANT UPDATE on table_name to <user_name>
GRANT DELETE on table_name to <User_name>
GRANT ALL on table_name to <user_name>
GRANT INSERT,SELECT on table_name to <User_name>
Procedures
GRANT EXECUTE on proc_name to <user_name>
Have a look into grant statement in books online for more previlage details
Thanks
Hari
MCDBA
"Nobster" <Norbert_Armstrong@.dub.Invesco.com> wrote in message
news:15a6001c44713$30c84b00$a301280a@.phx
.gbl...
> I am having a strange problem with certain databases.
> Some of the permissions I set for certain tables are not
> saving. I right click on the table,move over "All tasks"
> and select "Manage Permissions". I tick the permissions I
> want and click ok. When I exit and go back in the
> permissions I have set are gone.
> Can anyone explain.|||I have seen something similar before. Try testing the permissions in QA as
they may well have been correctly set. In my case when I used sp_helpprotect
or looked directly at sysprotects the permissions had been entered but EM
didn't display them correctly. Some time ago I did once 'debug' this
behaviour using profiler and posted up my findings in the replication group,
but unfortunately a search doesn't reveal them. Anyway if this corresponds
to your situation and you do profile it, please post up your findings.
Regards,
Paul Ibison

permission problem

In sql server 2005, what is minimum right a user need to view the content of stored-procedures? That is, in SSMS the user will be able to right click a stored-procedure and then select "Modify" to view the content, but the user has no permission to promote it?

Thanks!

I don't understand what you mean by promote because there is no promote permission but most of what you need is covered in this thread below. Hope this helps.

http://forums.asp.net/thread/1492092.aspx

|||

I want to allow the users to able to view the content of stored-procedure, but they are not allowed to modify or create stored-procedures. What permission should I give to the user? It is easy in sql server 2000, but 2005 seems has different implementation. Thanks.

|||That is db_datareader permission but that is in the database your user also need the server login because there are two permissions in SQL Server, the server and the database. So go back to that thread for how to create it. Hope this helps.

Permission Issues running DTS from Agent

We have a DTS which loads files using FTP to another
server. This will be used by app groups and will be run
via click of button using sp_start_job,

Somehow the userid running the job gets "not sysadmin to
run cmdshell.." Top fix that, we granted it to execute the
xp_cmdshell and also created a proxy account for SQL Agent
which is an admin on server and sql. However, still we get
permission errors like "...The needed permission is
missing to run command shell.." Please help . The DTS is
being called from SQl Agent job and we don't want an admin
ID to be used .Do you have 'exec master.dbo.' in from of xp_command... :confused:

Permission error on drillthrough

I've published a matrix type report through report builder to my report server, based on the model I created. When I click the subtotals to drillthrough, I receive the following error:

"The permissions granted to user 'Domain\userid' are insufficient for performing this operation. (rsAccessDenied)"

If I explicitly specify another report to tie to the drillthrough property on the entity via the model property page in SSMS, the drillthrough stuff works fine. But if I just rely on Reporting services to auto generate ( see "Autogenerated" here ) the clickthrough report I get the error above. Can anyone help?

Anyone?

Permission

Hi,
If I grant a user with the permission of db_datareader in EM, do I also
need to click on the Permission Button and check on every table/view in orde
r
for her to view them or the db_datareader means a user can view all
table/view by default?
Thanks
EdEd,
No. As BOL says "Can select all data from any user table in the database.".
Here is a script to prove it, assuming that your server has "SQL Server and
Windows" authentication.
Example:
-- DO NOT TEST IT IN A PRODUCTION SERVER
use master
go
execute sp_addlogin 'albert', 'b1r12-36', 'northwind'
go
use northwind
go
execute sp_grantdbaccess 'albert', 'albert'
go
execute sp_addrolemember 'db_datareader', 'albert'
go
select
a.*
from
openrowset('sqloledb', 'server=yourServer;uid=albert;pwd=b1r12-36',
northwind.dbo.orders) as a
go
execute sp_droprolemember 'db_datareader', 'albert'
go
execute sp_revokedbaccess @.name_in_db = 'albert'
go
use master
go
declare @.spid int
declare @.s varchar(50)
select
@.spid = spid
from
sysprocesses
where
loginame = 'albert'
and dbid = db_id('northwind')
if @.spid is not null
begin
set @.s = 'kill ' + ltrim(@.spid)
exec(@.s)
end
go
execute sp_droplogin @.loginame = 'albert'
go
AMB
"Ed" wrote:

> Hi,
> If I grant a user with the permission of db_datareader in EM, do I also
> need to click on the Permission Button and check on every table/view in or
der
> for her to view them or the db_datareader means a user can view all
> table/view by default?
> Thanks
> Ed