> But if I check 'use trusted connection' and press OK the priveliges
> don't work, Fred can change data.

> Q1) How do I prevent Fred from changing the data he shouldn't?

> Q2) Is it possible to get rid of this dialog box? I've already connected
> to the server as Fred, cant it automatically use the username and password
> I've already entered? Or do I have to go through this every single time I
> open a table?

"Harry Chance" <nospam.ple@se> wrote in message
news:25d_b.5855$0z4.53891228@news-text.cableinet.net...
> Hi, I'm experimenting with SQL security, creating users and access
> privileges, and so on. I'm using the 'pubs' database for practice.
>
> I've set the 'public' role to REVOKE all permissions on the table
> 'authors', (At least I think it's revoke - the box is empty, no tick or
> cross appears)
>
> I've created a role 'readonly' that grants SELECT on authors,
> and denies INSERT, UPDATE, DELETE.
>
> I've created a user 'Fred' with 'readonly' access on authors.
>
> Now, I connect to the database as Fred, then open the table.
> Up pops a dialog box 'SQL Server login' with fields to enter
> the LoginID and password, and a check box 'Use Trusted
> Connection'
>
> If I enter Fred's password, the access privileges work properly,
> they wont let Fred change the data in the table.
>
> But if I check 'use trusted connection' and press OK the priveliges
> don't work, Fred can change data.
>
> Q1) How do I prevent Fred from changing the data he shouldn't?
>
> Q2) Is it possible to get rid of this dialog box? I've already connected
> to the server as Fred, cant it automatically use the username and password
> I've already entered? Or do I have to go through this every single time I
> open a table?
>
>

> > But if I check 'use trusted connection' and press OK the priveliges
> > don't work, Fred can change data.
>
> You now logon using your windows account, not Fred's account. You might be
> logged on to Windows ad Administrator on the SQL Server machine, and the
> Administrators Windows groups are by default added as a login with
sysadmin
> privileges.
>
>
> > Q1) How do I prevent Fred from changing the data he shouldn't?
>
> Don't map the windows account.

> > Q2) Is it possible to get rid of this dialog box? I've already
connected
> > to the server as Fred, cant it automatically use the username and
password
> > I've already entered? Or do I have to go through this every single time
I
> > open a table?
>
> SQL Server cannot show any dialog boxes. It is either your client
> application or the database connectivity stuff (ODBC etc). You need to
check
> with the client app/dev tools.

> Tibor Karaszi, SQL Server MVP
> Archive at:
>
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
>
>
> "Harry Chance" <nospam.ple@se> wrote in message
> news:25d_b.5855$0z4.53891228@news-text.cableinet.net...
> > Hi, I'm experimenting with SQL security, creating users and access
> > privileges, and so on. I'm using the 'pubs' database for practice.
> >
> > I've set the 'public' role to REVOKE all permissions on the table
> > 'authors', (At least I think it's revoke - the box is empty, no tick or
> > cross appears)
> >
> > I've created a role 'readonly' that grants SELECT on authors,
> > and denies INSERT, UPDATE, DELETE.
> >
> > I've created a user 'Fred' with 'readonly' access on authors.
> >
> > Now, I connect to the database as Fred, then open the table.
> > Up pops a dialog box 'SQL Server login' with fields to enter
> > the LoginID and password, and a check box 'Use Trusted
> > Connection'
> >
> > If I enter Fred's password, the access privileges work properly,
> > they wont let Fred change the data in the table.
> >
> > But if I check 'use trusted connection' and press OK the priveliges
> > don't work, Fred can change data.
> >
> > Q1) How do I prevent Fred from changing the data he shouldn't?
> >
> > Q2) Is it possible to get rid of this dialog box? I've already
connected
> > to the server as Fred, cant it automatically use the username and
password
> > I've already entered? Or do I have to go through this every single time
I
> > open a table?
> >
> >
>
>

> > > Q1) How do I prevent Fred from changing the data he shouldn't?
> >
> > Don't map the windows account.
>
> I'm not sure I understand you. Are you saying that I SHOULD
>
> - create a windows login 'FRED' on my computer
> - login to my computer as FRED
> - connect to SQL SERVER using the FRED user.
>
> Or are you saying that I SHOULDN'T do that?
>
> It sounds like you're saying I shouldn't, but I'm not.
>
> I'm logged into windows using my own login, and connecting to SQLSERVER
> using 'sa' or 'FRED' from there.

"Harry Chance" <nospam.ple@se> wrote in message
news:_Ap_b.235$XJ2.2866384@news-text.cableinet.net...
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@hotmail.nomail.com> wrote
in
> message news:O19QfSf%23DHA.1392@tk2msftngp13.phx.gbl...
> > > But if I check 'use trusted connection' and press OK the priveliges
> > > don't work, Fred can change data.
> >
> > You now logon using your windows account, not Fred's account. You might
be
> > logged on to Windows ad Administrator on the SQL Server machine, and the
> > Administrators Windows groups are by default added as a login with
> sysadmin
> > privileges.
> >
> >
> > > Q1) How do I prevent Fred from changing the data he shouldn't?
> >
> > Don't map the windows account.
>
> I'm not sure I understand you. Are you saying that I SHOULD
>
> - create a windows login 'FRED' on my computer
> - login to my computer as FRED
> - connect to SQL SERVER using the FRED user.
>
> Or are you saying that I SHOULDN'T do that?
>
> It sounds like you're saying I shouldn't, but I'm not.
>
> I'm logged into windows using my own login, and connecting to SQLSERVER
> using 'sa' or 'FRED' from there.
>
> > > Q2) Is it possible to get rid of this dialog box? I've already
> connected
> > > to the server as Fred, cant it automatically use the username and
> password
> > > I've already entered? Or do I have to go through this every single
time
> I
> > > open a table?
> >
> > SQL Server cannot show any dialog boxes. It is either your client
> > application or the database connectivity stuff (ODBC etc). You need to
> check
> > with the client app/dev tools.
>
> Yes, I meant the client tool, SQL Server Enterprise Manager, not the
> database
> engine, obviously.
>
>
> > Tibor Karaszi, SQL Server MVP
> > Archive at:
> >
>
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
> >
> >
> > "Harry Chance" <nospam.ple@se> wrote in message
> > news:25d_b.5855$0z4.53891228@news-text.cableinet.net...
> > > Hi, I'm experimenting with SQL security, creating users and access
> > > privileges, and so on. I'm using the 'pubs' database for practice.
> > >
> > > I've set the 'public' role to REVOKE all permissions on the table
> > > 'authors', (At least I think it's revoke - the box is empty, no tick
or
> > > cross appears)
> > >
> > > I've created a role 'readonly' that grants SELECT on authors,
> > > and denies INSERT, UPDATE, DELETE.
> > >
> > > I've created a user 'Fred' with 'readonly' access on authors.
> > >
> > > Now, I connect to the database as Fred, then open the table.
> > > Up pops a dialog box 'SQL Server login' with fields to enter
> > > the LoginID and password, and a check box 'Use Trusted
> > > Connection'
> > >
> > > If I enter Fred's password, the access privileges work properly,
> > > they wont let Fred change the data in the table.
> > >
> > > But if I check 'use trusted connection' and press OK the priveliges
> > > don't work, Fred can change data.
> > >
> > > Q1) How do I prevent Fred from changing the data he shouldn't?
> > >
> > > Q2) Is it possible to get rid of this dialog box? I've already
> connected
> > > to the server as Fred, cant it automatically use the username and
> password
> > > I've already entered? Or do I have to go through this every single
time
> I
> > > open a table?
> > >
> > >
> >
> >
>
>

> Q2) Is it possible to get rid of this dialog box? I've already connected
> to the server as Fred, cant it automatically use the username and password
> I've already entered? Or do I have to go through this every single time I
> open a table?