sql login permissions: access

Showing posts with label access. Show all posts

Wednesday, March 21, 2012

Logreader access violation

Hi everyone,
I am setting up Transaction Replication on a MSSQL 2000 8.0.194. Everything
was going fine until the log reader stopped with a "An access violation
occurred" error.
I tried setting up a publication on another test database on the same server
to make sure the problem was not with the database it self and encountered
the same error.
In the ReplExceptLogRdr.log I am getting the following error:
Microsoft SQL Server Replication: Exception Stack Dump
************************************************** *****
Computer type is AT/AT COMPATIBLE.
Bios Version is DELL - 1
Phoenix ROM BIOS PLUS Version 1.10 A10Current local time is Fri Apr 13
15:16:41 2007
4 Intel x86 level 15, 2392 Mhz processor(s).
Windows NT 5.2 Build 3790 CSD .
MemoryLoad 62%TotalPhys 2047 MB
AvailPhys 1481 MBTotalPageFile 4095 MB
************************************************** *****
An exception was caught
in LOGREAD.EXE
* Exception Address = 00000001
* Exception Code = c0000005
*
************************************************** *****
Module NameReturn AddressLocation
[No Module Info]003678f000000001
[No Module Info]0040abd5003678f0
logread0040dec90040abd5
logread0040aa5c0040dec9
logread77e4f38c0040aa5c
kernel3200000000TerminateThread + 113
I have tested setting up replication using the same database on the users
reporting server and everything went fine. I have tried giving the folder
that contains the Distributor database and logfile access to "Everyone".
The Event Viewer show the following error and information:
[SQLSERVERAgent Error]
Microsoft SQL Server Replication : The LOGREAD.EXE executable is terminating
abnormally because it hit an exception. Exception Code c0000005. Check file
ReplExceptLogRdr.log for details.
[Information]
SubSystem Message - Job 'CL-SQL-CLUAT-1'
(0x7A865965F727AB4CA60CC19D5A5EEEA8), step 2 - An access violation occurred.
I tried changing the Logreaders agents verbose level to 3, but nothing extra
appeared in the sqlserver log.
Before I set up replication I had to change the logon the sqlserver agent
was running from the system account to a windows user with Admin rights. The
user also has sql server admin rights.
I am kind of stuck now as I don't know what else I can do to further
investigate the problem.
Any help would really be appreciated.
Thanks,
SQLNewbie
Hi Hillary,
Thanks for your time on this. I did post the contents of the
ReplExceptLogRdr.log in me initial post. It was located in the C:\Program
Files\Microsoft SQL Server\MSSQL\LOG folder. Is this the same log you are
referring to? I am not sure what you mean by (IIRC).
I wasn't sure if this was a bug so I haven't applied any patches.
Is this a user rights problem? That is the user running the SQL Server agent
has insufficient rights, because the user I created has Admin rights for
Windows and System Admin rights for SQL Server.
Thanks,
SQLNEwbie
"Hilary Cotter" wrote:

> I get these from time to time. Did you check ReplExceptLogRdr.log IIRC it
> will be found in windows\system32
> Also does this apply?
> http://support.microsoft.com/kb/872843/EN-US/
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "sqlnewbie" <sqlnewbie@.discussions.microsoft.com> wrote in message
> news:C5CCC218-2858-42BD-97BE-6C0B3AB1054E@.microsoft.com...
>
>
|||Hi,
Just a quick update on this problem. I applied SP4 and that has resolved the
problem.
Thanks
"sqlnewbie" wrote:
[vbcol=seagreen]
> Hi Hillary,
> Thanks for your time on this. I did post the contents of the
> ReplExceptLogRdr.log in me initial post. It was located in the C:\Program
> Files\Microsoft SQL Server\MSSQL\LOG folder. Is this the same log you are
> referring to? I am not sure what you mean by (IIRC).
> I wasn't sure if this was a bug so I haven't applied any patches.
> Is this a user rights problem? That is the user running the SQL Server agent
> has insufficient rights, because the user I created has Admin rights for
> Windows and System Admin rights for SQL Server.
> Thanks,
> SQLNEwbie
> "Hilary Cotter" wrote:

Monday, March 19, 2012

logon failed

hi
i am using crystal reports9 in vb6 with access database.

i Created one report called rep_customer
and wroted code on button_click event to call report from vb

vbcode---------------

public sub cust_report_button_click
connect
Set r = New ADODB.Recordset
ReportNameVar = App.Path & "\reports\rep_customers.rpt"
r.Open "select * from cust ", c, adOpenDynamic, adLockOptimistic
Set report = crxApplication.OpenReport(ReportNameVar, 1)
report.Database.SetDataSource r
CRViewer91.ReportSource = report
Rep_form.ViewReport
Rep_form.Show
End Sub
------------------

it;s working fine

i created one more report Called Purchase_order based
on two tables (purchase_order,purchase_order_details)
and wroted code on button_click event to call report from vb

vbcode---------------

public sub Purchase_report_button_click
connect
Set r = New ADODB.Recordset
ReportNameVar = App.Path & "\reports\rep_purchase.rpt"

r.Open "select Purchase_order.pono,purchase_order.date,
Purchase_order_details.Product_code ,Purchase_order_details.product_description
from purchase_order,purchase_order_details
where purchase_order.pono=purchase_order_details.pono ", c, adOpenDynamic, adLockOptimistic

Set report = crxApplication.OpenReport(ReportNameVar, 1)
report.Database.SetDataSource r
CRViewer91.ReportSource = report
Rep_form.ViewReport
Rep_form.Show
End Sub
---------------------
i am getting error like
logon failed
Details:Ado Error Code 0x80040e4d
Microsoft Jet Database Engine
Description not a valid password
sql stated 3031

Report is working fine in Desiging and Preview of Crystal Designer Environment
i am sure no problem in database connection username and password
because i am using same connection for customer report.

i hope Only the Problem while Calling from vb6 when report having 2 tables
Where is the Problem ?how Can i solve this One
Can Any one Help meOpen the report and Do verify Database

Monday, March 12, 2012

Logon auditing through trigger on sysprocesses?

I need to do auditing of logons, and selectively (depending on who does
them) of updates to a single table as well.
Furthermore, to make remote access to the audit log easier, I would prefer
to log all this in a table on the same server.
Full c2 auditing would give me far too much information (and I haven't even
checked if it is supported in MSDE). It would probably reduce performance
as well.
Full auditing of all updates to just that one table where I need it, would
give too much information too: one certain application that's running on
the local machine should be left out.
Now I think I could get everything I want done through triggers, but
there's one problem.
The 'sysprocesses' table seems like a good place to detect logons, if I
could create an INSERT trigger that copies username and hostname to a
logging table for each new record.
The problem: the db engine won't let me create a trigger on the
sysprocesses table, it keeps saying "access denied" no matter how I try to
override and change permissions.
I realize that there could be some danger too, if executing a trigger would
create a new process. But will it? I would expect that a trigger is
executed in the context of the process that initiated the change.
hi,
Lucvdv wrote:
>...
> The 'sysprocesses' table seems like a good place to detect logons, if
> I could create an INSERT trigger that copies username and hostname to
> a logging table for each new record.
> The problem: the db engine won't let me create a trigger on the
> sysprocesses table, it keeps saying "access denied" no matter how I
> try to override and change permissions.
>
sysprocesses is a "fake" table, in the sense it is materialized on demand,
and you can't perform INSERT, UPDATE, or DELETE operations on this kind of
table no matter how much privilege you have on the system, and of course you
can not write trigger on it..
as regard auditing, try perhaps having a look at
http://www.windowsitpro.com/Article/...434/26434.html ...
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.15.0 - DbaMgr ver 0.60.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||Andrea Montanari wrote:
> hi,
> Lucvdv wrote:
> sysprocesses is a "fake" table, in the sense it is materialized on demand,
> and you can't perform INSERT, UPDATE, or DELETE operations on this kind of
> table no matter how much privilege you have on the system, and of course you
> can not write trigger on it..
> as regard auditing, try perhaps having a look at
> http://www.windowsitpro.com/Article/...434/26434.html ...
Thanks.
It wasn't of much help though

The article is about finding out whodunnit in a situation where
everyone has the sa password, and suggests giving each user his own
database to connect to.
In my case each user has his own logon credentials, all changes except
those made by a certain account should be logged, and changes made by
any user should be visible to all.
That part shouldn't be too hard to do, the difficulty is knowing when
someone logs on and from where.
|||On Tue, 30 Aug 2005 19:31:30 +0200, Lucvdv <name@.null.net> wrote:

> In my case each user has his own logon credentials, all changes except
> those made by a certain account should be logged, and changes made by
> any user should be visible to all.
> That part shouldn't be too hard to do, the difficulty is knowing when
> someone logs on and from where.
The change logging part is working, with a simple trigger.
There is a performance hit of course, but it doesn't seem to be too much
(I've tested it in a real live database for about an hour, and didn't see
any noticeable system slowdown or abnormally high CPU use).
In case someone wants to use it as a model, this is the 'simple' version
with a test table I started from:
The 'Test' table just has 2 columns Col1 and Col2, both integer, with Col1
as primary key.
The 'TestLog' table must exist before the trigger is added, with columns
(in this case) DT DateTime, spid smallint, Col1 int, oldCol2 int, newCol2
int, user varchar(128), host varchar(128), prog varchar(128).
In the real test I included an identity column, so sequence numbers will be
missing if someone deletes log lines.
CREATE TRIGGER [trgTest] ON dbo.Test
FOR UPDATE
AS
DECLARE @.user varchar(128), @.host varchar(128), @.prog varchar(128)
BEGIN
SELECT @.user = RTRIM([nt_username]), @.host = RTRIM([hostname]),
@.prog = RTRIM([program_name])
FROM [master].[dbo].[sysprocesses] WHERE spid=@.@.SPID
IF @.prog<>'My application'
INSERT INTO [TestLog]
SELECT GetDate(), @.@.SPID, [Inserted].[Col1], [Deleted].[Col2],
[Inserted].[Col2], @.user, @.host, @.prog
FROM [Inserted] INNER JOIN [Deleted]
ON [Inserted].[Col1]=[Deleted].[Col1]
WHERE [Inserted].[Col2]<>[Deleted].[Col2]
END
|||have a look at
http://www.sqlservercentral.com/colu...qlprofiler.asp
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.15.0 - DbaMgr ver 0.60.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply

Logn error

I'm having a bizarre error occur with one specific user. For starters, I hav
e
an SQL back-end with an Access data project front end. All of my users are
members of a SQLUSERS group, with the correct permissions.
When one specific user, Zack, logs into the db, he does so under the
SQLUSERS group. Everything runs smoothly until Zack tries to perform a
particular action that imports data from a fixed-width text file on CD. When
he does this, the system adds Zack as a separate user, even though he's
already part of the SQLUSERS group. The new Zack user inherits the
permissions from SQLUSERS, and during the data import process the system
creates new tables for Zack. Some fields don't have the correct data types,
which causes an error. Zack becomes the owner of these new import tables.
When any other member of SQLUSERS performs the same action, the system
deletes the data from, and reuses the existing tables.
Has anyone encountered anything similar to this?Hi
SQL Server will not create new users on it's own, therefore you have some
code that will be doing this. If your code is creating new (staging) tables
then zack will own them if he is not the dbo or a sysadmin. If the tables do
not already exist I would expect the same behaviour for other users that hav
e
the same privileges for Zack unless your code is conditional on who is
running it. If permissions are granted to the SQLUsers group other users may
be able to access and truncate the tables. For these users the code must kno
w
that Zack is the owner and referencing them using it.
You should review the code and/or use profiler to see what statements are
being sent to the database.
John
"mike" wrote:

> I'm having a bizarre error occur with one specific user. For starters, I h
ave
> an SQL back-end with an Access data project front end. All of my users are
> members of a SQLUSERS group, with the correct permissions.
> When one specific user, Zack, logs into the db, he does so under the
> SQLUSERS group. Everything runs smoothly until Zack tries to perform a
> particular action that imports data from a fixed-width text file on CD. Wh
en
> he does this, the system adds Zack as a separate user, even though he's
> already part of the SQLUSERS group. The new Zack user inherits the
> permissions from SQLUSERS, and during the data import process the system
> creates new tables for Zack. Some fields don't have the correct data types
,
> which causes an error. Zack becomes the owner of these new import tables.
> When any other member of SQLUSERS performs the same action, the system
> deletes the data from, and reuses the existing tables.
> Has anyone encountered anything similar to this?

login's last access

Hi,
In SQL 7, is there a way to know when a (NT and SQL auth)
login last went into the SQL server?
Thanks,
dennisDennis,
SQL Server can log event information into ERRORLOG file for review by the system administrator. At a
minimum, you should log failed connection attempts to SQL Server and review the log regularly. When
possible, save these logs to a different hard drive than the one on which data files are stored.
But for this following should have been already configured which, enables auditing of connections in
SQL Server:
Expand a server group.
Right-click a server, and then click Properties.
On the Security tab, under Audit Level, click Failure/success/all.
You must stop and restart the server for this setting to take effect.
- Vishal

Friday, March 9, 2012

Logins & its associated tables.

Hi All,

Does anyone knows the query/Script, which gives the list of tables the logins( SQL & Windows Authenticated) have access to.

In other words, the output should be logins and their access to respective tables.

Thanks for all your help.

Naj,

Exec sp_helprotect

Run the above statement in your database, you will see all your objects, not only tables and who has access to those objects. More info, refer Books online - search sp_helprotect

Sample output

Owner Object Grantee Grantor ProtectType Action Column

- - --

dbo NarasApp Naras\jay dbo Grant Select (All+New)

dbo NarasApp public dbo Grant Select (All+New)

Here Object column shows the name of object and Grantee column shows who has access on the object.

Thanks.

Naras.

login with security on records based on data (not tables)

Hi,
Is it possible to define a login on an SQL SERVER that has only access to
certain records in a table based on the data in the records?
For example login LoginA can should only be able to
update/read/insert/delete records with the Field Company = 'A'. LoginB only
those for Company = 'B'.
Is there some way for doing this?
I should be able to do something like this with views (one view for each
login), and define in the views the records they can use. But I would really
need something directly to the table, or if not with only one view (one view
for all the login's).
Is there some way, soem trick or I don't knwo what the produce such a
behaviour?
Any help or hint would be really aprpeciated!
Thanks a lot in advance,
Pieter
See if this helps:
http://vyaskn.tripod.com/row_level_s..._databases.htm
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
"DraguVaso" <pietercoucke@.hotmail.com> wrote in message
news:ugcklHoiEHA.2356@.TK2MSFTNGP10.phx.gbl...
Hi,
Is it possible to define a login on an SQL SERVER that has only access to
certain records in a table based on the data in the records?
For example login LoginA can should only be able to
update/read/insert/delete records with the Field Company = 'A'. LoginB only
those for Company = 'B'.
Is there some way for doing this?
I should be able to do something like this with views (one view for each
login), and define in the views the records they can use. But I would really
need something directly to the table, or if not with only one view (one view
for all the login's).
Is there some way, soem trick or I don't knwo what the produce such a
behaviour?
Any help or hint would be really aprpeciated!
Thanks a lot in advance,
Pieter
|||Thanks man! You did a nice job there!! This wil be very helpfull!!!
"Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
news:esiJ5RoiEHA.212@.TK2MSFTNGP10.phx.gbl...
> See if this helps:
> http://vyaskn.tripod.com/row_level_s..._databases.htm
> --
> HTH,
> Vyas, MVP (SQL Server)
> http://vyaskn.tripod.com/
>
> "DraguVaso" <pietercoucke@.hotmail.com> wrote in message
> news:ugcklHoiEHA.2356@.TK2MSFTNGP10.phx.gbl...
> Hi,
> Is it possible to define a login on an SQL SERVER that has only access to
> certain records in a table based on the data in the records?
> For example login LoginA can should only be able to
> update/read/insert/delete records with the Field Company = 'A'. LoginB
only
> those for Company = 'B'.
> Is there some way for doing this?
> I should be able to do something like this with views (one view for each
> login), and define in the views the records they can use. But I would
really
> need something directly to the table, or if not with only one view (one
view
> for all the login's).
> Is there some way, soem trick or I don't knwo what the produce such a
> behaviour?
> Any help or hint would be really aprpeciated!
> Thanks a lot in advance,
> Pieter
>
>

login with security on records based on data (not tables)

Hi,
Is it possible to define a login on an SQL SERVER that has only access to
certain records in a table based on the data in the records?
For example login LoginA can should only be able to
update/read/insert/delete records with the Field Company = 'A'. LoginB only
those for Company = 'B'.
Is there some way for doing this?
I should be able to do something like this with views (one view for each
login), and define in the views the records they can use. But I would really
need something directly to the table, or if not with only one view (one view
for all the login's).
Is there some way, soem trick or I don't knwo what the produce such a
behaviour?
Any help or hint would be really aprpeciated!
Thanks a lot in advance,
PieterSee if this helps:
es.htm" target="_blank">http://vyaskn.tripod.com/ row_level...as
es.htm
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
"DraguVaso" <pietercoucke@.hotmail.com> wrote in message
news:ugcklHoiEHA.2356@.TK2MSFTNGP10.phx.gbl...
Hi,
Is it possible to define a login on an SQL SERVER that has only access to
certain records in a table based on the data in the records?
For example login LoginA can should only be able to
update/read/insert/delete records with the Field Company = 'A'. LoginB only
those for Company = 'B'.
Is there some way for doing this?
I should be able to do something like this with views (one view for each
login), and define in the views the records they can use. But I would really
need something directly to the table, or if not with only one view (one view
for all the login's).
Is there some way, soem trick or I don't knwo what the produce such a
behaviour?
Any help or hint would be really aprpeciated!
Thanks a lot in advance,
Pieter|||Thanks man! You did a nice job there!! This wil be very helpfull!!!
"Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
news:esiJ5RoiEHA.212@.TK2MSFTNGP10.phx.gbl...
> See if this helps:
> es.htm" target="_blank">http://vyaskn.tripod.com/ row_level...as
es.htm
> --
> HTH,
> Vyas, MVP (SQL Server)
> http://vyaskn.tripod.com/
>
> "DraguVaso" <pietercoucke@.hotmail.com> wrote in message
> news:ugcklHoiEHA.2356@.TK2MSFTNGP10.phx.gbl...
> Hi,
> Is it possible to define a login on an SQL SERVER that has only access to
> certain records in a table based on the data in the records?
> For example login LoginA can should only be able to
> update/read/insert/delete records with the Field Company = 'A'. LoginB
only
> those for Company = 'B'.
> Is there some way for doing this?
> I should be able to do something like this with views (one view for each
> login), and define in the views the records they can use. But I would
really
> need something directly to the table, or if not with only one view (one
view
> for all the login's).
> Is there some way, soem trick or I don't knwo what the produce such a
> behaviour?
> Any help or hint would be really aprpeciated!
> Thanks a lot in advance,
> Pieter
>
>

login with security on records based on data (not tables)

Hi,
Is it possible to define a login on an SQL SERVER that has only access to
certain records in a table based on the data in the records?
For example login LoginA can should only be able to
update/read/insert/delete records with the Field Company = 'A'. LoginB only
those for Company = 'B'.
Is there some way for doing this?
I should be able to do something like this with views (one view for each
login), and define in the views the records they can use. But I would really
need something directly to the table, or if not with only one view (one view
for all the login's).
Is there some way, soem trick or I don't knwo what the produce such a
behaviour?
Any help or hint would be really aprpeciated!
Thanks a lot in advance,
PieterSee if this helps:
http://vyaskn.tripod.com/row_level_security_in_sql_server_databases.htm
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
"DraguVaso" <pietercoucke@.hotmail.com> wrote in message
news:ugcklHoiEHA.2356@.TK2MSFTNGP10.phx.gbl...
Hi,
Is it possible to define a login on an SQL SERVER that has only access to
certain records in a table based on the data in the records?
For example login LoginA can should only be able to
update/read/insert/delete records with the Field Company = 'A'. LoginB only
those for Company = 'B'.
Is there some way for doing this?
I should be able to do something like this with views (one view for each
login), and define in the views the records they can use. But I would really
need something directly to the table, or if not with only one view (one view
for all the login's).
Is there some way, soem trick or I don't knwo what the produce such a
behaviour?
Any help or hint would be really aprpeciated!
Thanks a lot in advance,
Pieter|||Thanks man! You did a nice job there!! This wil be very helpfull!!!
"Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
news:esiJ5RoiEHA.212@.TK2MSFTNGP10.phx.gbl...
> See if this helps:
> http://vyaskn.tripod.com/row_level_security_in_sql_server_databases.htm
> --
> HTH,
> Vyas, MVP (SQL Server)
> http://vyaskn.tripod.com/
>
> "DraguVaso" <pietercoucke@.hotmail.com> wrote in message
> news:ugcklHoiEHA.2356@.TK2MSFTNGP10.phx.gbl...
> Hi,
> Is it possible to define a login on an SQL SERVER that has only access to
> certain records in a table based on the data in the records?
> For example login LoginA can should only be able to
> update/read/insert/delete records with the Field Company = 'A'. LoginB
only
> those for Company = 'B'.
> Is there some way for doing this?
> I should be able to do something like this with views (one view for each
> login), and define in the views the records they can use. But I would
really
> need something directly to the table, or if not with only one view (one
view
> for all the login's).
> Is there some way, soem trick or I don't knwo what the produce such a
> behaviour?
> Any help or hint would be really aprpeciated!
> Thanks a lot in advance,
> Pieter
>
>

Wednesday, March 7, 2012

Login setting by IP range?

"Uri Dimant" <urid@.iscar.co.il> glsD:%23TPXYYHMGHA.2696@.TK2MSFTNGP14.phx .gbl...
> Well, have a look at APPLICATION ROLE in the BOL
> "Lau Lei Cheong" <leu_lc@.yehoo.com.hk> wrote in message
> news:O2MjaNHMGHA.3960@.TK2MSFTNGP09.phx.gbl...
>

Login setting by IP range?

Hello,
In mysql we can set to allow a user to login and access certain
table from certain host, is there anything close to that in MSSQL 2000/2005?
Actually, we just need to control certain users unable to use the
Domain Creditial to access certain databases/tables except in the company's
IP, while we need to make sure for certain databases/tables, the client can
be accessed anywhere.
If not, is there any kind of database connection proxies that we can
use to control the connections, or this there any tutorial on how to relay
database datas so we can write a program to do access check?
Looking forward for reply.
Regards,
Lau Lei CheongLau
How do they (users) access the database? Did you divide them into Groups?
You can DENY access on table for the user .
In addition , you can specify a hostname in connection string.
"Lau Lei Cheong" <leu_lc@.yehoo.com.hk> wrote in message
news:uXz12iGMGHA.3856@.TK2MSFTNGP12.phx.gbl...
> Hello,
> In mysql we can set to allow a user to login and access certain
> table from certain host, is there anything close to that in MSSQL
> 2000/2005?
> Actually, we just need to control certain users unable to use the
> Domain Creditial to access certain databases/tables except in the
> company's IP, while we need to make sure for certain databases/tables, the
> client can be accessed anywhere.
> If not, is there any kind of database connection proxies that we
> can use to control the connections, or this there any tutorial on how to
> relay database datas so we can write a program to do access check?
> Looking forward for reply.
> Regards,
> Lau Lei Cheong
>|||Yes, I know I can divide users into groups but that's not the problem.
The problem is, as the subject line suggested, I can't find way to limit
certain user's access to certain database/table from certain IP.
"Uri Dimant" <urid@.iscar.co.il> glsD:%23aeJm7GMGHA.500@.TK2MSFTNGP15.phx.gbl...[vbc
ol=seagreen]
> Lau
> How do they (users) access the database? Did you divide them into Groups?
> You can DENY access on table for the user .
> In addition , you can specify a hostname in connection string.
>
> "Lau Lei Cheong" <leu_lc@.yehoo.com.hk> wrote in message
> news:uXz12iGMGHA.3856@.TK2MSFTNGP12.phx.gbl...
>[/vbcol]|||Well, have a look at APPLICATION ROLE in the BOL
"Lau Lei Cheong" <leu_lc@.yehoo.com.hk> wrote in message
news:O2MjaNHMGHA.3960@.TK2MSFTNGP09.phx.gbl...
> Yes, I know I can divide users into groups but that's not the problem.
> The problem is, as the subject line suggested, I can't find way to limit
> certain user's access to certain database/table from certain IP.
> "Uri Dimant" <urid@.iscar.co.il>
> glsD:%23aeJm7GMGHA.500@.TK2MSFTNGP15.phx.gbl...
>|||Sorry, I've reviewed application role, but it don't help in this case
because:
1) I need to control access by IP, not by application
2) Some users under the control are application developers who can write
their own application.
But your information is really useful on other area, thanks.

"Uri Dimant" <urid@.iscar.co.il> glsD:%23TPXYYHMGHA.2696@.TK2MSFTNGP14.phx.gbl...[vb
col=seagreen]
> Well, have a look at APPLICATION ROLE in the BOL
> "Lau Lei Cheong" <leu_lc@.yehoo.com.hk> wrote in message
> news:O2MjaNHMGHA.3960@.TK2MSFTNGP09.phx.gbl...
>[/vbcol]

Login setting by IP range?

Hello,
In MySQL we can set to allow a user to login and access certain
table from certain host, is there anything close to that in MSSQL 2000/2005?
Actually, we just need to control certain users unable to use the
Domain Creditial to access certain databases/tables except in the company's
IP, while we need to make sure for certain databases/tables, the client can
be accessed anywhere.
If not, is there any kind of database connection proxies that we can
use to control the connections, or this there any tutorial on how to relay
database datas so we can write a program to do access check?
Looking forward for reply.
Regards,
Lau Lei CheongLau
How do they (users) access the database? Did you divide them into Groups?
You can DENY access on table for the user .
In addition , you can specify a hostname in connection string.
"Lau Lei Cheong" <leu_lc@.yehoo.com.hk> wrote in message
news:uXz12iGMGHA.3856@.TK2MSFTNGP12.phx.gbl...
> Hello,
> In MySQL we can set to allow a user to login and access certain
> table from certain host, is there anything close to that in MSSQL
> 2000/2005?
> Actually, we just need to control certain users unable to use the
> Domain Creditial to access certain databases/tables except in the
> company's IP, while we need to make sure for certain databases/tables, the
> client can be accessed anywhere.
> If not, is there any kind of database connection proxies that we
> can use to control the connections, or this there any tutorial on how to
> relay database datas so we can write a program to do access check?
> Looking forward for reply.
> Regards,
> Lau Lei Cheong
>|||Yes, I know I can divide users into groups but that's not the problem.
The problem is, as the subject line suggested, I can't find way to limit
certain user's access to certain database/table from certain IP.
"Uri Dimant" <urid@.iscar.co.il> ¼¶¼g©ó¶l¥ó·s»D:%23aeJm7GMGHA.500@.TK2MSFTNGP15.phx.gbl...
> Lau
> How do they (users) access the database? Did you divide them into Groups?
> You can DENY access on table for the user .
> In addition , you can specify a hostname in connection string.
>
> "Lau Lei Cheong" <leu_lc@.yehoo.com.hk> wrote in message
> news:uXz12iGMGHA.3856@.TK2MSFTNGP12.phx.gbl...
>> Hello,
>> In MySQL we can set to allow a user to login and access certain
>> table from certain host, is there anything close to that in MSSQL
>> 2000/2005?
>> Actually, we just need to control certain users unable to use the
>> Domain Creditial to access certain databases/tables except in the
>> company's IP, while we need to make sure for certain databases/tables,
>> the client can be accessed anywhere.
>> If not, is there any kind of database connection proxies that we
>> can use to control the connections, or this there any tutorial on how to
>> relay database datas so we can write a program to do access check?
>> Looking forward for reply.
>> Regards,
>> Lau Lei Cheong
>>
>|||Well, have a look at APPLICATION ROLE in the BOL
"Lau Lei Cheong" <leu_lc@.yehoo.com.hk> wrote in message
news:O2MjaNHMGHA.3960@.TK2MSFTNGP09.phx.gbl...
> Yes, I know I can divide users into groups but that's not the problem.
> The problem is, as the subject line suggested, I can't find way to limit
> certain user's access to certain database/table from certain IP.
> "Uri Dimant" <urid@.iscar.co.il>
> ¼¶¼g©ó¶l¥ó·s»D:%23aeJm7GMGHA.500@.TK2MSFTNGP15.phx.gbl...
>> Lau
>> How do they (users) access the database? Did you divide them into Groups?
>> You can DENY access on table for the user .
>> In addition , you can specify a hostname in connection string.
>>
>> "Lau Lei Cheong" <leu_lc@.yehoo.com.hk> wrote in message
>> news:uXz12iGMGHA.3856@.TK2MSFTNGP12.phx.gbl...
>> Hello,
>> In MySQL we can set to allow a user to login and access certain
>> table from certain host, is there anything close to that in MSSQL
>> 2000/2005?
>> Actually, we just need to control certain users unable to use the
>> Domain Creditial to access certain databases/tables except in the
>> company's IP, while we need to make sure for certain databases/tables,
>> the client can be accessed anywhere.
>> If not, is there any kind of database connection proxies that we
>> can use to control the connections, or this there any tutorial on how to
>> relay database datas so we can write a program to do access check?
>> Looking forward for reply.
>> Regards,
>> Lau Lei Cheong
>>
>>
>|||Sorry, I've reviewed application role, but it don't help in this case
because:
1) I need to control access by IP, not by application
2) Some users under the control are application developers who can write
their own application.
But your information is really useful on other area, thanks. :)
"Uri Dimant" <urid@.iscar.co.il> ¼¶¼g©ó¶l¥ó·s»D:%23TPXYYHMGHA.2696@.TK2MSFTNGP14.phx.gbl...
> Well, have a look at APPLICATION ROLE in the BOL
> "Lau Lei Cheong" <leu_lc@.yehoo.com.hk> wrote in message
> news:O2MjaNHMGHA.3960@.TK2MSFTNGP09.phx.gbl...
>> Yes, I know I can divide users into groups but that's not the problem.
>> The problem is, as the subject line suggested, I can't find way to limit
>> certain user's access to certain database/table from certain IP.
>> "Uri Dimant" <urid@.iscar.co.il> ¼¶¼g©ó¶l¥ó·s»D:%23aeJm7GMGHA.500@.TK2MSFTNGP15.phx.gbl...
>> Lau
>> How do they (users) access the database? Did you divide them into
>> Groups?
>> You can DENY access on table for the user .
>> In addition , you can specify a hostname in connection string.
>>
>> "Lau Lei Cheong" <leu_lc@.yehoo.com.hk> wrote in message
>> news:uXz12iGMGHA.3856@.TK2MSFTNGP12.phx.gbl...
>> Hello,
>> In MySQL we can set to allow a user to login and access certain
>> table from certain host, is there anything close to that in MSSQL
>> 2000/2005?
>> Actually, we just need to control certain users unable to use
>> the Domain Creditial to access certain databases/tables except in the
>> company's IP, while we need to make sure for certain databases/tables,
>> the client can be accessed anywhere.
>> If not, is there any kind of database connection proxies that we
>> can use to control the connections, or this there any tutorial on how
>> to relay database datas so we can write a program to do access check?
>> Looking forward for reply.
>> Regards,
>> Lau Lei Cheong
>>
>>
>>
>

Friday, February 24, 2012

Login problems with SQL Express edition.

Hi,

when running my application and trying to access an SqlServer express edition DB, I encounter the folowing error:

Cannot open user default database. Login failed.

Does any one know how this problem can be resolved?

It is the SQL Server that comes togather with the VS2005.

Thank you!

The problem doesnt occure when i run the application using the default web server (cassini). It only occures when I run it using IIS.

But how can i run the application on IIS and use the DB?

Thnaks.

Login problems on Vista before and after SP2

Hi:

I have recently updated my Sony Vaio and installed Windows Vista. After installing I could not access my SQL databases because of the NT Authorisation / sysadmin problem.

I have since installed SQLServer SP2 (Nov CTP) but the problem persists. If I run SQL Management Studio under the administrative account I can login just fine. I have also made sure the 'NT AUTHORITY\SYSTEM' is marked for sysadmin access (which it is).

The eror I get is Error 18456.

What else can I do to gain access via Windows Authorisation?

TIA,

MartinH.

Hello Martin,

Are you are logging into the Vista under any domain credentials? If so please add the domain user as the local system administrator and try.

Thanks,

Prakash P [MSFT]

|||

Prakash:

Sorry to be so dense, but I'm not quite sure what you are asking.

I am using Vista on a portable that does not connect to any Domain Server. The locally created user belongs to the Administrators group.

Is this what you meant by 'please add the domain user as the local system administrator'?

Thanks for your reply.

Martin.

|||

Here is a workaround for resolving this issue:

1. Run SSMS “As an Administrator”, by right clicking the SSMS icon and clicking “Run As Administrator” option and select Windows Authentication and login to SQL Server.

2. In the Object Explorer node select the Security node and then select Logins node. Add a new login for your windows account and select Windows Authentication. Also go the Server Roles pane in the same window and select sysadmin role for it.

3. Close SSMS and start it normally and give your domain account and select Windows Authentication and you will not have any issues thereafter.

|||

Rajesh:

That did the trick!!

Thanks very much for your help.

Regards,

Martin.

login problems

After migrating SQL Server 2000 from NT to 2003, I have a login failure for
one user when trying to access a database.When I go to the user's login
properties, Database Access, I check the Permit box for the database for the
user and click OK.
I get an Microsoft SQL-DMO error message:
Error 21002:[SQL-DMO]User 'MW_DBA' already exists.
How can I fix this?
Antonin
Antonin
It sounds like your logins and User ID's have got out of synch. Try running
the following on the master database
EXEC sp_change_users_login 'Auto_Fix', 'MW_DBA'
Hope this helps
John
|||Hi John,
Thanks for your reply. I tried it and got this:
The number of orphaned users fixed by updating users was 0.
The number of orphaned users fixed by adding new logins and then updating
users was 0.
Antonin
"John Bandettini" <JohnBandettini@.discussions.microsoft.com> wrote in
message news:D9637F5C-58DF-4940-8E0F-9E4D2A507B51@.microsoft.com...
> Antonin
> It sounds like your logins and User ID's have got out of synch. Try
> running
> the following on the master database
> EXEC sp_change_users_login 'Auto_Fix', 'MW_DBA'
> Hope this helps
> John

login problems

After migrating SQL Server 2000 from NT to 2003, I have a login failure for
one user when trying to access a database.When I go to the user's login
properties, Database Access, I check the Permit box for the database for the
user and click OK.
I get an Microsoft SQL-DMO error message:
Error 21002:[SQL-DMO]User 'MW_DBA' already exists.
How can I fix this?
AntoninAntonin
It sounds like your logins and User ID's have got out of synch. Try running
the following on the master database
EXEC sp_change_users_login 'Auto_Fix', 'MW_DBA'
Hope this helps
John|||Hi John,
Thanks for your reply. I tried it and got this:
The number of orphaned users fixed by updating users was 0.
The number of orphaned users fixed by adding new logins and then updating
users was 0.
Antonin
"John Bandettini" <JohnBandettini@.discussions.microsoft.com> wrote in
message news:D9637F5C-58DF-4940-8E0F-9E4D2A507B51@.microsoft.com...
> Antonin
> It sounds like your logins and User ID's have got out of synch. Try
> running
> the following on the master database
> EXEC sp_change_users_login 'Auto_Fix', 'MW_DBA'
> Hope this helps
> John

Monday, February 20, 2012

login permissions

I created a new user and wanted them to only be able to access one database.
For that one database on the "database access" tab I checked only that one
database and checked the "public" and "db_datareader" database roles for that
one database.
When I log in through QA they can also see the "master", "msdb" and "tempdb"
databases. And they run select queries on the "master" database. How can I
prevent this? I only want them to be able to "see" the "stone" database.
Thanks,
--
Dan D.You can't. They have access to these databases because they contain a guest
user. This cannot be removed from master or tempdb however it can be removed
from msdb. Note that if you do remove it from msdb then only sysadmins will
be able to create/manage jobs and save DTS packages to the server.
--
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Dan D." <DanD@.discussions.microsoft.com> wrote in message
news:A66E6531-4E69-4A7A-A244-3360757DF11E@.microsoft.com...
>I created a new user and wanted them to only be able to access one
>database.
> For that one database on the "database access" tab I checked only that one
> database and checked the "public" and "db_datareader" database roles for
> that
> one database.
> When I log in through QA they can also see the "master", "msdb" and
> "tempdb"
> databases. And they run select queries on the "master" database. How can I
> prevent this? I only want them to be able to "see" the "stone" database.
> Thanks,
> --
> Dan D.|||I'm not sure that I understand. If I remove the guest user from msdb no one
will be able to create/manage job and save dts packages. If I add each user
who I want to be able to create/manage jobs and save dts packages to msdb and
remove guest will that be ok? What happens if I remove guest from master?
I don't understand why microsoft would allow anyone to be able to query the
system databases. Is there a reason?
Thanks,
"Jasper Smith" wrote:
> You can't. They have access to these databases because they contain a guest
> user. This cannot be removed from master or tempdb however it can be removed
> from msdb. Note that if you do remove it from msdb then only sysadmins will
> be able to create/manage jobs and save DTS packages to the server.
> --
> HTH
> Jasper Smith (SQL Server MVP)
> http://www.sqldbatips.com
> I support PASS - the definitive, global
> community for SQL Server professionals -
> http://www.sqlpass.org
> "Dan D." <DanD@.discussions.microsoft.com> wrote in message
> news:A66E6531-4E69-4A7A-A244-3360757DF11E@.microsoft.com...
> >I created a new user and wanted them to only be able to access one
> >database.
> > For that one database on the "database access" tab I checked only that one
> > database and checked the "public" and "db_datareader" database roles for
> > that
> > one database.
> >
> > When I log in through QA they can also see the "master", "msdb" and
> > "tempdb"
> > databases. And they run select queries on the "master" database. How can I
> > prevent this? I only want them to be able to "see" the "stone" database.
> >
> > Thanks,
> > --
> > Dan D.
>
>|||> I'm not sure that I understand. If I remove the guest user from msdb no one
> will be able to create/manage job and save dts packages.
Sysadmins will.
> If I add each user
> who I want to be able to create/manage jobs and save dts packages to msdb and
> remove guest will that be ok?
Yes. But again, no need to add sysadmins.
> What happens if I remove guest from master?
You can't do that.
> I don't understand why microsoft would allow anyone to be able to query the
> system databases.
Compare it to Windows registry. A Windows user need permissions to (parts of) the registry.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Dan D." <DanD@.discussions.microsoft.com> wrote in message
news:D8D7D43F-177F-41A3-B4AB-4E3D0BD38262@.microsoft.com...
> I'm not sure that I understand. If I remove the guest user from msdb no one
> will be able to create/manage job and save dts packages. If I add each user
> who I want to be able to create/manage jobs and save dts packages to msdb and
> remove guest will that be ok? What happens if I remove guest from master?
> I don't understand why microsoft would allow anyone to be able to query the
> system databases. Is there a reason?
> Thanks,
> "Jasper Smith" wrote:
>> You can't. They have access to these databases because they contain a guest
>> user. This cannot be removed from master or tempdb however it can be removed
>> from msdb. Note that if you do remove it from msdb then only sysadmins will
>> be able to create/manage jobs and save DTS packages to the server.
>> --
>> HTH
>> Jasper Smith (SQL Server MVP)
>> http://www.sqldbatips.com
>> I support PASS - the definitive, global
>> community for SQL Server professionals -
>> http://www.sqlpass.org
>> "Dan D." <DanD@.discussions.microsoft.com> wrote in message
>> news:A66E6531-4E69-4A7A-A244-3360757DF11E@.microsoft.com...
>> >I created a new user and wanted them to only be able to access one
>> >database.
>> > For that one database on the "database access" tab I checked only that one
>> > database and checked the "public" and "db_datareader" database roles for
>> > that
>> > one database.
>> >
>> > When I log in through QA they can also see the "master", "msdb" and
>> > "tempdb"
>> > databases. And they run select queries on the "master" database. How can I
>> > prevent this? I only want them to be able to "see" the "stone" database.
>> >
>> > Thanks,
>> > --
>> > Dan D.
>>|||>Compare it to Windows registry. A Windows user need permissions to (parts
of) >the registry.
Can the guest user in the master database change anything? We're trying to
set up a login for a client. We created a database for them and we want them
only to be able to read the data. We don't want them to be able to change any
data in the database we created for them or to change any data in master. Is
there a better way to do this?
Thanks,
"Tibor Karaszi" wrote:
> > I'm not sure that I understand. If I remove the guest user from msdb no one
> > will be able to create/manage job and save dts packages.
> Sysadmins will.
>
> > If I add each user
> > who I want to be able to create/manage jobs and save dts packages to msdb and
> > remove guest will that be ok?
> Yes. But again, no need to add sysadmins.
>
> > What happens if I remove guest from master?
> You can't do that.
>
> > I don't understand why microsoft would allow anyone to be able to query the
> > system databases.
> Compare it to Windows registry. A Windows user need permissions to (parts of) the registry.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Dan D." <DanD@.discussions.microsoft.com> wrote in message
> news:D8D7D43F-177F-41A3-B4AB-4E3D0BD38262@.microsoft.com...
> > I'm not sure that I understand. If I remove the guest user from msdb no one
> > will be able to create/manage job and save dts packages. If I add each user
> > who I want to be able to create/manage jobs and save dts packages to msdb and
> > remove guest will that be ok? What happens if I remove guest from master?
> > I don't understand why microsoft would allow anyone to be able to query the
> > system databases. Is there a reason?
> >
> > Thanks,
> >
> > "Jasper Smith" wrote:
> >
> >> You can't. They have access to these databases because they contain a guest
> >> user. This cannot be removed from master or tempdb however it can be removed
> >> from msdb. Note that if you do remove it from msdb then only sysadmins will
> >> be able to create/manage jobs and save DTS packages to the server.
> >>
> >> --
> >> HTH
> >>
> >> Jasper Smith (SQL Server MVP)
> >> http://www.sqldbatips.com
> >> I support PASS - the definitive, global
> >> community for SQL Server professionals -
> >> http://www.sqlpass.org
> >>
> >> "Dan D." <DanD@.discussions.microsoft.com> wrote in message
> >> news:A66E6531-4E69-4A7A-A244-3360757DF11E@.microsoft.com...
> >> >I created a new user and wanted them to only be able to access one
> >> >database.
> >> > For that one database on the "database access" tab I checked only that one
> >> > database and checked the "public" and "db_datareader" database roles for
> >> > that
> >> > one database.
> >> >
> >> > When I log in through QA they can also see the "master", "msdb" and
> >> > "tempdb"
> >> > databases. And they run select queries on the "master" database. How can I
> >> > prevent this? I only want them to be able to "see" the "stone" database.
> >> >
> >> > Thanks,
> >> > --
> >> > Dan D.
> >>
> >>
> >>
>
>|||> Can the guest user in the master database change anything?
No. Not unless you give the user various permissions to do that.
> We're trying to
> set up a login for a client. We created a database for them and we want them
> only to be able to read the data. We don't want them to be able to change any
> data in the database we created for them or to change any data in master. Is
> there a better way to do this?
You can grant use user the role db_datareader, which means that the user has SELECT permissions for
all tables in your user database. Or, of course grant explicit SELECT permissions on each object.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Dan D." <DanD@.discussions.microsoft.com> wrote in message
news:8744AFA2-5B48-417F-B1D1-4DCF8200B597@.microsoft.com...
> >Compare it to Windows registry. A Windows user need permissions to (parts
> of) >the registry.
> Can the guest user in the master database change anything? We're trying to
> set up a login for a client. We created a database for them and we want them
> only to be able to read the data. We don't want them to be able to change any
> data in the database we created for them or to change any data in master. Is
> there a better way to do this?
> Thanks,
>
> "Tibor Karaszi" wrote:
>> > I'm not sure that I understand. If I remove the guest user from msdb no one
>> > will be able to create/manage job and save dts packages.
>> Sysadmins will.
>>
>> > If I add each user
>> > who I want to be able to create/manage jobs and save dts packages to msdb and
>> > remove guest will that be ok?
>> Yes. But again, no need to add sysadmins.
>>
>> > What happens if I remove guest from master?
>> You can't do that.
>>
>> > I don't understand why microsoft would allow anyone to be able to query the
>> > system databases.
>> Compare it to Windows registry. A Windows user need permissions to (parts of) the registry.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "Dan D." <DanD@.discussions.microsoft.com> wrote in message
>> news:D8D7D43F-177F-41A3-B4AB-4E3D0BD38262@.microsoft.com...
>> > I'm not sure that I understand. If I remove the guest user from msdb no one
>> > will be able to create/manage job and save dts packages. If I add each user
>> > who I want to be able to create/manage jobs and save dts packages to msdb and
>> > remove guest will that be ok? What happens if I remove guest from master?
>> > I don't understand why microsoft would allow anyone to be able to query the
>> > system databases. Is there a reason?
>> >
>> > Thanks,
>> >
>> > "Jasper Smith" wrote:
>> >
>> >> You can't. They have access to these databases because they contain a guest
>> >> user. This cannot be removed from master or tempdb however it can be removed
>> >> from msdb. Note that if you do remove it from msdb then only sysadmins will
>> >> be able to create/manage jobs and save DTS packages to the server.
>> >>
>> >> --
>> >> HTH
>> >>
>> >> Jasper Smith (SQL Server MVP)
>> >> http://www.sqldbatips.com
>> >> I support PASS - the definitive, global
>> >> community for SQL Server professionals -
>> >> http://www.sqlpass.org
>> >>
>> >> "Dan D." <DanD@.discussions.microsoft.com> wrote in message
>> >> news:A66E6531-4E69-4A7A-A244-3360757DF11E@.microsoft.com...
>> >> >I created a new user and wanted them to only be able to access one
>> >> >database.
>> >> > For that one database on the "database access" tab I checked only that one
>> >> > database and checked the "public" and "db_datareader" database roles for
>> >> > that
>> >> > one database.
>> >> >
>> >> > When I log in through QA they can also see the "master", "msdb" and
>> >> > "tempdb"
>> >> > databases. And they run select queries on the "master" database. How can I
>> >> > prevent this? I only want them to be able to "see" the "stone" database.
>> >> >
>> >> > Thanks,
>> >> > --
>> >> > Dan D.
>> >>
>> >>
>> >>
>>|||Tibor is correct in using the Windows Registry analogy. There are some
things that EVERY granted login needs access to, most notibly the system
catalogues, how else would they know to go to their respective user
databases? That has to be queried.
Now, that being said, the default security in master is somewhat lax. You
can google several sites that go through securing your system databases, but
you should test these strategies thouroughly before implementing on a
production system.
Most objects are granted permission on the system databases through the
public default database role. Since every database user is a member of this
role, including the guest account, every system login would have access to
these. As a system admin, however, you are explicitly aliased to THE dbo in
EVERY database; so, permission checks are usually bypassed.
You would be best served by removing permissions from public and then
creating seperate roles and granting specific permissions to each of these
roles, but then you would have to add every login to the master database as
some user, mapped to one of your roles. This can be combersome and
migrating to a new host could be tedious. But if you want security, this is
what you must do.
One of the best sites I've seen is www.sqlsecurity.com. They go through
quite a bit of this information with practical examples.
Best of luck.
Sincerely,
Anthony Thomas
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uttkITQOFHA.3808@.TK2MSFTNGP14.phx.gbl...
> Can the guest user in the master database change anything?
No. Not unless you give the user various permissions to do that.
> We're trying to
> set up a login for a client. We created a database for them and we want
them
> only to be able to read the data. We don't want them to be able to change
any
> data in the database we created for them or to change any data in master.
Is
> there a better way to do this?
You can grant use user the role db_datareader, which means that the user has
SELECT permissions for
all tables in your user database. Or, of course grant explicit SELECT
permissions on each object.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Dan D." <DanD@.discussions.microsoft.com> wrote in message
news:8744AFA2-5B48-417F-B1D1-4DCF8200B597@.microsoft.com...
> >Compare it to Windows registry. A Windows user need permissions to (parts
> of) >the registry.
> Can the guest user in the master database change anything? We're trying to
> set up a login for a client. We created a database for them and we want
them
> only to be able to read the data. We don't want them to be able to change
any
> data in the database we created for them or to change any data in master.
Is
> there a better way to do this?
> Thanks,
>
> "Tibor Karaszi" wrote:
>> > I'm not sure that I understand. If I remove the guest user from msdb no
one
>> > will be able to create/manage job and save dts packages.
>> Sysadmins will.
>>
>> > If I add each user
>> > who I want to be able to create/manage jobs and save dts packages to
msdb and
>> > remove guest will that be ok?
>> Yes. But again, no need to add sysadmins.
>>
>> > What happens if I remove guest from master?
>> You can't do that.
>>
>> > I don't understand why microsoft would allow anyone to be able to query
the
>> > system databases.
>> Compare it to Windows registry. A Windows user need permissions to (parts
of) the registry.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "Dan D." <DanD@.discussions.microsoft.com> wrote in message
>> news:D8D7D43F-177F-41A3-B4AB-4E3D0BD38262@.microsoft.com...
>> > I'm not sure that I understand. If I remove the guest user from msdb no
one
>> > will be able to create/manage job and save dts packages. If I add each
user
>> > who I want to be able to create/manage jobs and save dts packages to
msdb and
>> > remove guest will that be ok? What happens if I remove guest from
master?
>> > I don't understand why microsoft would allow anyone to be able to query
the
>> > system databases. Is there a reason?
>> >
>> > Thanks,
>> >
>> > "Jasper Smith" wrote:
>> >
>> >> You can't. They have access to these databases because they contain a
guest
>> >> user. This cannot be removed from master or tempdb however it can be
removed
>> >> from msdb. Note that if you do remove it from msdb then only sysadmins
will
>> >> be able to create/manage jobs and save DTS packages to the server.
>> >>
>> >> --
>> >> HTH
>> >>
>> >> Jasper Smith (SQL Server MVP)
>> >> http://www.sqldbatips.com
>> >> I support PASS - the definitive, global
>> >> community for SQL Server professionals -
>> >> http://www.sqlpass.org
>> >>
>> >> "Dan D." <DanD@.discussions.microsoft.com> wrote in message
>> >> news:A66E6531-4E69-4A7A-A244-3360757DF11E@.microsoft.com...
>> >> >I created a new user and wanted them to only be able to access one
>> >> >database.
>> >> > For that one database on the "database access" tab I checked only
that one
>> >> > database and checked the "public" and "db_datareader" database roles
for
>> >> > that
>> >> > one database.
>> >> >
>> >> > When I log in through QA they can also see the "master", "msdb" and
>> >> > "tempdb"
>> >> > databases. And they run select queries on the "master" database. How
can I
>> >> > prevent this? I only want them to be able to "see" the "stone"
database.
>> >> >
>> >> > Thanks,
>> >> > --
>> >> > Dan D.
>> >>
>> >>
>> >>
>>

Login name is <none>

Hi,
SQL 2000. I've created a login for W2K Group Mydomain\Domain admins,
and gave it access to the DB. When I add it as a user, the login name
changes to <none>. I don't understand why. It does not happen to the
other logins.
If I am missing something basic, I would appreciate a pointer to a KB
or technet article or whatever, explaining what that is.
Thanks a lot,
PeterHi Peter,
My name is Michael and I would like to thank you for using Microsoft
newsgroup.
According to your description, I understand that you create a login in SQL
Server 2000 for domain account and add this login as a user of a specific
database. However, the login name changes to <none>. In other words, the
user in the database does not match any logins. If I have misunderstood,
please feel free to let me know.
Based on my experience, it seems that you experience the orphaned user.
Logins are associated to users by the security identifier (SID), and if the
SID is inconsistent after you move a database, SQL Server may deny the user
access to the database. This problem is known as an orphaned user.
The suggestions in the following articles should address your problem.
274188 PRB: "Troubleshooting Orphaned Users" Topic in Books Online is
Incomplete
http://support.microsoft.com/?id=274188
314546 HOW TO: Move Databases Between Computers That Are Running SQL Server
http://support.microsoft.com/?id=314546
Please feel free to post in the group if this solves your problem or if you
would like further assistance.
Regards,
Michael Shao
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.|||Hi Michael,
Thanks for the reply.
You understand the problem correctly.
I think I might have orphaned users as well, but I believe I the
answer to this particular problem which involves domain admins group
was in KB 819261
Regards,
Peter
On Wed, 14 Jan 2004 12:41:09 GMT, v-yshao@.online.microsoft.com ("Yuan
Shao") wrote:

quote:

>Hi Peter,
>My name is Michael and I would like to thank you for using Microsoft
>newsgroup.
>According to your description, I understand that you create a login in SQL
>Server 2000 for domain account and add this login as a user of a specific
>database. However, the login name changes to <none>. In other words, the
>user in the database does not match any logins. If I have misunderstood,
>please feel free to let me know.
>Based on my experience, it seems that you experience the orphaned user.
>Logins are associated to users by the security identifier (SID), and if the
>SID is inconsistent after you move a database, SQL Server may deny the user
>access to the database. This problem is known as an orphaned user.
>The suggestions in the following articles should address your problem.
>274188 PRB: "Troubleshooting Orphaned Users" Topic in Books Online is
>Incomplete
>http://support.microsoft.com/?id=274188
>314546 HOW TO: Move Databases Between Computers That Are Running SQL Server
>http://support.microsoft.com/?id=314546
>Please feel free to post in the group if this solves your problem or if you
>would like further assistance.
>Regards,
>Michael Shao
>Microsoft Online Partner Support
>Get Secure! - www.microsoft.com/security
>This posting is provided "as is" with no warranties and confers no rights.

|||Hi Peter,
Thanks for your feedback. Is this orphaned user problem resolved? Did you
mean you tried the workarounds mentioned in the following article and the
problem was resolved?
819261 BUG: Error 15023, 15024 or 21002 Occurs When You Try to Add a Domain
http://support.microsoft.com/?id=819261
If not, I would like you to try the suggestions(KB 274188 & KB 314546)
mentioned in my previous post to see if it fix the orphaned user problem.
I am looking forward to hearing from you soon.
Regards,
Michael Shao
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.|||HI Michael,
Yes, this particular problem seems to be resolved by following the
workaround in that KB article, although I haven't had time to really
confirm it. Just looking at EM, it seems okay now.
What I was trying to say was I think there is a completely separate
problem with some other ophaned users and I will be looking at those
other articles next week. If they suggest using sp_change_users_login,
then please take a look on a problem I will post later on today on
with that sp in the subject.
Thanks,
Peter
On Thu, 15 Jan 2004 06:33:53 GMT, v-yshao@.online.microsoft.com ("Yuan
Shao") wrote:

quote:

>Hi Peter,
>Thanks for your feedback. Is this orphaned user problem resolved? Did you
>mean you tried the workarounds mentioned in the following article and the
>problem was resolved?
>819261 BUG: Error 15023, 15024 or 21002 Occurs When You Try to Add a Domain
>http://support.microsoft.com/?id=819261
>If not, I would like you to try the suggestions(KB 274188 & KB 314546)
>mentioned in my previous post to see if it fix the orphaned user problem.
>I am looking forward to hearing from you soon.
>Regards,
>Michael Shao
>Microsoft Online Partner Support
>Get Secure! - www.microsoft.com/security
>This posting is provided "as is" with no warranties and confers no rights.

Wednesday, March 21, 2012

Logreader access violation

Monday, March 19, 2012

logon failed

Monday, March 12, 2012

Logon auditing through trigger on sysprocesses?

Logn error

login's last access

Friday, March 9, 2012

Logins & its associated tables.

login with security on records based on data (not tables)

login with security on records based on data (not tables)

login with security on records based on data (not tables)

Wednesday, March 7, 2012

Login setting by IP range?

Login setting by IP range?

Login setting by IP range?

Friday, February 24, 2012

Login problems with SQL Express edition.

Login problems on Vista before and after SP2

login problems

login problems

Monday, February 20, 2012

login permissions

login permissions

login permissions

Login name is <none>

sql login permissions

Blog Archive

About Me