Permissions when using trusted connection

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