I want my users to use trusted connection when logging in to the SQL Server,
and that goes successful, but the strange thing is that the SQL Server
seemes to think that the logged in user is "sa" which is not so good, cause
then the user has all the pemissons. I want to set the permissions manually
for each "trusted connection"-user. What am I doing wrong. Probably an easy
question.When you login with a trusted connection (no userid/password in connection
string), you are logged in as that account rather than the 'sa' SQL Server
account. If the Windows account has sa-like permissions, then a likely
cause is that the user is a member of the SQL Server sysadmin server role.
Members of the Windows local Administrators group are sysadmin role members
by default so perhaps the account is an admin on the SQL Server box.
A best practice in SQL Server security admin is to grant permissions to
roles rather than individual users. This simplifies administration because
you can control access via role membership.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Uffe" <uffe.waldero@.home.se> wrote in message
news:ubEysOg0DHA.1684@.TK2MSFTNGP12.phx.gbl...
> I want my users to use trusted connection when logging in to the SQL
Server,
> and that goes successful, but the strange thing is that the SQL Server
> seemes to think that the logged in user is "sa" which is not so good,
cause
> then the user has all the pemissons. I want to set the permissions
manually
> for each "trusted connection"-user. What am I doing wrong. Probably an
easy
> question.
>|||I suspect the NT logins are members of a group for which you have given
sysadmin privileges.
Check all of your sql roles and remember everyone is a member of the public
role.
--
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Uffe" <uffe.waldero@.home.se> wrote in message
news:ubEysOg0DHA.1684@.TK2MSFTNGP12.phx.gbl...
> I want my users to use trusted connection when logging in to the SQL
Server,
> and that goes successful, but the strange thing is that the SQL Server
> seemes to think that the logged in user is "sa" which is not so good,
cause
> then the user has all the pemissons. I want to set the permissions
manually
> for each "trusted connection"-user. What am I doing wrong. Probably an
easy
> question.
>|||Why don't you make these users members of the db_datawriter fixed database
role? You can find more info in books online but here's an example:
EXEC sp_addrolemember 'db_datawriter', 'username'
--
Carlos E. Rojas
SQL Server MVP
Co-Author SQL Server 2000 Programming by Example
"Eugene" <eliu@.ctisinc.com> wrote in message
news:30D10394-6085-4C6B-A268-9E3BFA75833A@.microsoft.com...
> HI,
> I am having a little different issue. When my windows users login into sql
server with trustes connection, they can select the data but no update and
insert. It said permission denied. But I have put them into DBO group or
role and have all permission on database. What went wrong ? Please help.
Many thanks.sql
Showing posts with label strange. Show all posts
Showing posts with label strange. Show all posts
Monday, March 26, 2012
Wednesday, March 21, 2012
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
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
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 :
ows
ot
n
hown
e to
n is
t is
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.
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.
Subscribe to:
Posts (Atom)