Permissions not saved

HI,
SQL2k ( SP3a) ( I also install SP3a for the tools on my
workstation)
I encounter a strange problem when I try to set permissions on VIEW
Grant select on dbo.view_name to PUBLIC
Deny insert, update, delete on dbo.view_name to PUBLIC
With QA, when I execute thoses 2 commands ( Grant and Deny), those
commands are =93completed successfully=94 BUT =85. when I go to EM / Vi=
ew /
properties / permissions, nothing have been saved for PUBLIC role. If
I change persmission by clicking , go out of EM and when I go back in,
the permissions are back the way they were before I changed them
(nothing is saved)
Also, when I execute "sp_helprotect view_name" the result is:
Server: Msg 15330, Level 11, State 1, Procedure sp_helprotect, Line 346
There are no matching rows on which to report.
I tried with a =93user =93 instead of the PUBLIC role, and I have the sam=
e
problem.
I tries using SA account, and I have the same problem
I tried working on others worksations or on the console without success
I tried re-install =93sp3a=94 for the tools on my workstation without
success.
I will try to use PROFILER tomorrow.
Is it a bug with SP3a ? Is it a collation problem ?
Any idea '
Thank you
DannyHi Danny,
I believe I know the answer to part of your information. When EXEC
sp_helprotect <view name> returns a result set "There are no matching rows
on which to report", it means that a view by the name specificed does not
exist in the current database. Double check the database name listed in
the drop down at the top of Query Analyzer. Verify the database name shown
is the database name that contains the view name.
The second part of your information is more difficult to test. If you
register a server via SQL Enterprise Manager and you register using an
account that is part of the System Admin server role, you should be able to
make any permissions changes you wish and have them stick. My suspicion is
you registered SQL Server in SQL Enterprise Manager with an account that is
not a member of the System Admin role.
Please let me know what role the account holds within SQL Server.
Thanks.
Gary Whitley
This posting is provided "AS IS" with no warranties, and confers no rights.|||Thank you Gary,
I Verified that the BD's name shown is the database name that contains th=
e view
name.
I Verified that I registered SQL Server in SQL Enterprise Manager with an=
account that is
a member of the System Admin role. I also tried to register SQL Server in=
SQL
Enterprise Manager ans QA with "SA" .
Additional information: I have 2 similars DB on the same server machine.
For the first one, persmissions are saved and I can verify with SP_Helpro=
tect
For the other one, permissions are not saved and SP_Helprotect returns a=
result set "There are no matching rows on which to report",
Tonight dbreindex and Updatestat will be executed
Thank you
Danny
"Gary Whitley [MSFT]" a =E9crit :

quote:

---

> Hi Danny,
> I believe I know the answer to part of your information. When EXEC
> sp_helprotect <view name> returns a result set "There are no matching r=

---

ows

quote:

---

> on which to report", it means that a view by the name specificed does n=

---

ot

quote:

---

> exist in the current database. Double check the database name listed i=

---

n

quote:

---

> the drop down at the top of Query Analyzer. Verify the database name s=

---

hown

quote:

---

> is the database name that contains the view name.
> The second part of your information is more difficult to test. If you
> register a server via SQL Enterprise Manager and you register using an
> account that is part of the System Admin server role, you should be abl=

---

e to

quote:

---

> make any permissions changes you wish and have them stick. My suspicio=

---

n is

quote:

---

> you registered SQL Server in SQL Enterprise Manager with an account tha=

---

t is

quote:

---

> not a member of the System Admin role.
> Please let me know what role the account holds within SQL Server.
> Thanks.
> Gary Whitley
> This posting is provided "AS IS" with no warranties, and confers no rig=

---

hts.|||Danny,
Thank you for using the Microsoft newsgroups as your source for technical
information. In this particular case the complexity of your issue will
require in-depth troubleshooting and will not be best served by newsgroup
support. Please go to;
http://support.microsoft.com/common...=fh;en-us;cntac
tms
Select your region from the map and follow the instructions for contacting
our telephone support centers in your area.
Thank you.
Gary Whitley
This posting is provided "AS IS" with no warranties, and confers no rights.

Permission concept MSSQL05

Hello
I am looking for any advices or drafts for a new concept how to handle
all the permission to our DB Servers. We are still using SQL2k and
want migrate to SQL05 till the end of the year. Therefore we want to
improve our security with less complexity.
Our productive environment contains 7 MSSQL 2000 Servers with almost
70 DBs. Each Server has its own security-DB where all the rolls are
stored and their permission to each DB.
An advantage of this is that we can run a refresh of all permissions
anytime. On the other hand we have to add all the permissions manually
to that security-DB. This DB does not contain the schemas which will
come which the migration of SQL05.
I would appreciate some information or any links about security
concepts in SQL2005. I am not looking for a complete solution, just
for ideas.
Regards
Martin FrickDBA (martin-za.frick@.ubs.com) writes:
> I am looking for any advices or drafts for a new concept how to handle
> all the permission to our DB Servers. We are still using SQL2k and
> want migrate to SQL05 till the end of the year. Therefore we want to
> improve our security with less complexity.
> Our productive environment contains 7 MSSQL 2000 Servers with almost
> 70 DBs. Each Server has its own security-DB where all the rolls are
> stored and their permission to each DB.
> An advantage of this is that we can run a refresh of all permissions
> anytime. On the other hand we have to add all the permissions manually
> to that security-DB. This DB does not contain the schemas which will
> come which the migration of SQL05.
Well, anything that worked in SQL 2000 will work in SQL 2005, but of
course SQL 2005 will offer you more choices, and there are of course
some chances you want to use that. Not the least the fact that you
can grant rights on schema level can make things simpler, and for
instance you don't have to grant rights on every single procedure.
There are also more privileges, for instance users need VIEW DEFINITION
to be able to see the metadata for an object.
Overall, there are tons of changes in the security area.
So there is a risk that you will have to vamp up your security database
quite a bit.

> I would appreciate some information or any links about security
> concepts in SQL2005. I am not looking for a complete solution, just
> for ideas.
The obvious start is Books Online for SQL 2005. There is a
chapter about Security considerations under Database Engine.
For more details on a special features when it comes to stored procedure,
you may be interested in an article on my web site:
http://www.sommarskog.se/grantperm.html
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx