To all,
I am running SQL Server 2000 SP3 and I have 6 database running on one
machine, in one instance, which has 4GB of ram. All the database are running
fine, except for one. The users are complaining of slowness and query
timeouts. The network traffic is fine and I am not sure what else it could
be. I have also noticed that one of the connections had a waittype called
'CXPACKET'
Does anyone know what could be causing the problem?
Thanks in advance,
BelindaCan you post the query this happens on? How many CPU does the server has and
speed? How are the disks set-up?
CXPACKET It means that one thread of the query is is waiting for a message
packet from another, and the one it is waiting on is either blocked by a
traditional cause or has hit some sort of parallelism bug.
Try Using MAXDOP(1) option on your queries, else make sure you have the
latest service packs applied.
Yovan Fernandez
"Belinda Rodriguez" <rodrigub@.symbol.com> wrote in message
news:e$971JxdDHA.3332@.TK2MSFTNGP09.phx.gbl...
> To all,
> I am running SQL Server 2000 SP3 and I have 6 database running on one
> machine, in one instance, which has 4GB of ram. All the database are
running
> fine, except for one. The users are complaining of slowness and query
> timeouts. The network traffic is fine and I am not sure what else it
could
> be. I have also noticed that one of the connections had a waittype called
> 'CXPACKET'
> Does anyone know what could be causing the problem?
> Thanks in advance,
> Belinda
>
Showing posts with label instance. Show all posts
Showing posts with label instance. Show all posts
Sunday, March 11, 2012
Database Roles
If I set up a user with a database role of dbo, does that automatically give
him dbo rights on all objects in the database. For instance, can the user
then execute a stored procedure in the database, or do you have to explicitly
go in and put an "X" in the execute permission checkbox next to the stored
procedure?
Message posted via http://www.droptable.com
Robert,
By dbo I assume that you assigned the user to the db_owner role. If you
have, then that person has rights to do absolutely anything, within the
bounds of that database.
If you don't want him to be able to do absolutely anything, then place him
in another role and grant that role the needed rights.
RLF
"Robert R via droptable.com" <u3288@.uwe> wrote in message
news:571d2aa2f02d8@.uwe...
> If I set up a user with a database role of dbo, does that automatically
> give
> him dbo rights on all objects in the database. For instance, can the user
> then execute a stored procedure in the database, or do you have to
> explicitly
> go in and put an "X" in the execute permission checkbox next to the stored
> procedure?
> --
> Message posted via http://www.droptable.com
|||When granting the database owner role to the user, do you also need to put a
check mark (such as select, insert, update, delete) for access to a table, or
Exec for a stored procedure for the user to have those specified permissions
on the object, because when the user is granted the database owner role, all
the check boxes are blank.
Russell Fields wrote:[vbcol=seagreen]
>Robert,
>By dbo I assume that you assigned the user to the db_owner role. If you
>have, then that person has rights to do absolutely anything, within the
>bounds of that database.
>If you don't want him to be able to do absolutely anything, then place him
>in another role and grant that role the needed rights.
>RLF
>[quoted text clipped - 3 lines]
Message posted via http://www.droptable.com
|||Making a user member of 'db_owner' role will ensure that the user 'has all
permissions in the database'. No need of to put additinal check mark for
select etc.
"Robert R via droptable.com" wrote:
> When granting the database owner role to the user, do you also need to put a
> check mark (such as select, insert, update, delete) for access to a table, or
> Exec for a stored procedure for the user to have those specified permissions
> on the object, because when the user is granted the database owner role, all
> the check boxes are blank.
him dbo rights on all objects in the database. For instance, can the user
then execute a stored procedure in the database, or do you have to explicitly
go in and put an "X" in the execute permission checkbox next to the stored
procedure?
Message posted via http://www.droptable.com
Robert,
By dbo I assume that you assigned the user to the db_owner role. If you
have, then that person has rights to do absolutely anything, within the
bounds of that database.
If you don't want him to be able to do absolutely anything, then place him
in another role and grant that role the needed rights.
RLF
"Robert R via droptable.com" <u3288@.uwe> wrote in message
news:571d2aa2f02d8@.uwe...
> If I set up a user with a database role of dbo, does that automatically
> give
> him dbo rights on all objects in the database. For instance, can the user
> then execute a stored procedure in the database, or do you have to
> explicitly
> go in and put an "X" in the execute permission checkbox next to the stored
> procedure?
> --
> Message posted via http://www.droptable.com
|||When granting the database owner role to the user, do you also need to put a
check mark (such as select, insert, update, delete) for access to a table, or
Exec for a stored procedure for the user to have those specified permissions
on the object, because when the user is granted the database owner role, all
the check boxes are blank.
Russell Fields wrote:[vbcol=seagreen]
>Robert,
>By dbo I assume that you assigned the user to the db_owner role. If you
>have, then that person has rights to do absolutely anything, within the
>bounds of that database.
>If you don't want him to be able to do absolutely anything, then place him
>in another role and grant that role the needed rights.
>RLF
>[quoted text clipped - 3 lines]
Message posted via http://www.droptable.com
|||Making a user member of 'db_owner' role will ensure that the user 'has all
permissions in the database'. No need of to put additinal check mark for
select etc.
"Robert R via droptable.com" wrote:
> When granting the database owner role to the user, do you also need to put a
> check mark (such as select, insert, update, delete) for access to a table, or
> Exec for a stored procedure for the user to have those specified permissions
> on the object, because when the user is granted the database owner role, all
> the check boxes are blank.
Database Roles
If I set up a user with a database role of dbo, does that automatically give
him dbo rights on all objects in the database. For instance, can the user
then execute a stored procedure in the database, or do you have to explicitl
y
go in and put an "X" in the execute permission checkbox next to the stored
procedure?
Message posted via http://www.droptable.comRobert,
By dbo I assume that you assigned the user to the db_owner role. If you
have, then that person has rights to do absolutely anything, within the
bounds of that database.
If you don't want him to be able to do absolutely anything, then place him
in another role and grant that role the needed rights.
RLF
"Robert R via droptable.com" <u3288@.uwe> wrote in message
news:571d2aa2f02d8@.uwe...
> If I set up a user with a database role of dbo, does that automatically
> give
> him dbo rights on all objects in the database. For instance, can the user
> then execute a stored procedure in the database, or do you have to
> explicitly
> go in and put an "X" in the execute permission checkbox next to the stored
> procedure?
> --
> Message posted via http://www.droptable.com|||When granting the database owner role to the user, do you also need to put a
check mark (such as select, insert, update, delete) for access to a table, o
r
Exec for a stored procedure for the user to have those specified permissions
on the object, because when the user is granted the database owner role, all
the check boxes are blank.
Russell Fields wrote:[vbcol=seagreen]
>Robert,
>By dbo I assume that you assigned the user to the db_owner role. If you
>have, then that person has rights to do absolutely anything, within the
>bounds of that database.
>If you don't want him to be able to do absolutely anything, then place him
>in another role and grant that role the needed rights.
>RLF
>
>[quoted text clipped - 3 lines]
Message posted via http://www.droptable.com|||Making a user member of 'db_owner' role will ensure that the user 'has all
permissions in the database'. No need of to put additinal check mark for
select etc.
"Robert R via droptable.com" wrote:
> When granting the database owner role to the user, do you also need to put
a
> check mark (such as select, insert, update, delete) for access to a table,
or
> Exec for a stored procedure for the user to have those specified permissio
ns
> on the object, because when the user is granted the database owner role, a
ll
> the check boxes are blank.
him dbo rights on all objects in the database. For instance, can the user
then execute a stored procedure in the database, or do you have to explicitl
y
go in and put an "X" in the execute permission checkbox next to the stored
procedure?
Message posted via http://www.droptable.comRobert,
By dbo I assume that you assigned the user to the db_owner role. If you
have, then that person has rights to do absolutely anything, within the
bounds of that database.
If you don't want him to be able to do absolutely anything, then place him
in another role and grant that role the needed rights.
RLF
"Robert R via droptable.com" <u3288@.uwe> wrote in message
news:571d2aa2f02d8@.uwe...
> If I set up a user with a database role of dbo, does that automatically
> give
> him dbo rights on all objects in the database. For instance, can the user
> then execute a stored procedure in the database, or do you have to
> explicitly
> go in and put an "X" in the execute permission checkbox next to the stored
> procedure?
> --
> Message posted via http://www.droptable.com|||When granting the database owner role to the user, do you also need to put a
check mark (such as select, insert, update, delete) for access to a table, o
r
Exec for a stored procedure for the user to have those specified permissions
on the object, because when the user is granted the database owner role, all
the check boxes are blank.
Russell Fields wrote:[vbcol=seagreen]
>Robert,
>By dbo I assume that you assigned the user to the db_owner role. If you
>have, then that person has rights to do absolutely anything, within the
>bounds of that database.
>If you don't want him to be able to do absolutely anything, then place him
>in another role and grant that role the needed rights.
>RLF
>
>[quoted text clipped - 3 lines]
Message posted via http://www.droptable.com|||Making a user member of 'db_owner' role will ensure that the user 'has all
permissions in the database'. No need of to put additinal check mark for
select etc.
"Robert R via droptable.com" wrote:
> When granting the database owner role to the user, do you also need to put
a
> check mark (such as select, insert, update, delete) for access to a table,
or
> Exec for a stored procedure for the user to have those specified permissio
ns
> on the object, because when the user is granted the database owner role, a
ll
> the check boxes are blank.
Database Roles
If I set up a user with a database role of dbo, does that automatically give
him dbo rights on all objects in the database. For instance, can the user
then execute a stored procedure in the database, or do you have to explicitly
go in and put an "X" in the execute permission checkbox next to the stored
procedure?
--
Message posted via http://www.sqlmonster.comRobert,
By dbo I assume that you assigned the user to the db_owner role. If you
have, then that person has rights to do absolutely anything, within the
bounds of that database.
If you don't want him to be able to do absolutely anything, then place him
in another role and grant that role the needed rights.
RLF
"Robert R via SQLMonster.com" <u3288@.uwe> wrote in message
news:571d2aa2f02d8@.uwe...
> If I set up a user with a database role of dbo, does that automatically
> give
> him dbo rights on all objects in the database. For instance, can the user
> then execute a stored procedure in the database, or do you have to
> explicitly
> go in and put an "X" in the execute permission checkbox next to the stored
> procedure?
> --
> Message posted via http://www.sqlmonster.com|||When granting the database owner role to the user, do you also need to put a
check mark (such as select, insert, update, delete) for access to a table, or
Exec for a stored procedure for the user to have those specified permissions
on the object, because when the user is granted the database owner role, all
the check boxes are blank.
Russell Fields wrote:
>Robert,
>By dbo I assume that you assigned the user to the db_owner role. If you
>have, then that person has rights to do absolutely anything, within the
>bounds of that database.
>If you don't want him to be able to do absolutely anything, then place him
>in another role and grant that role the needed rights.
>RLF
>> If I set up a user with a database role of dbo, does that automatically
>> give
>[quoted text clipped - 3 lines]
>> go in and put an "X" in the execute permission checkbox next to the stored
>> procedure?
--
Message posted via http://www.sqlmonster.com|||Making a user member of 'db_owner' role will ensure that the user 'has all
permissions in the database'. No need of to put additinal check mark for
select etc.
"Robert R via SQLMonster.com" wrote:
> When granting the database owner role to the user, do you also need to put a
> check mark (such as select, insert, update, delete) for access to a table, or
> Exec for a stored procedure for the user to have those specified permissions
> on the object, because when the user is granted the database owner role, all
> the check boxes are blank.
him dbo rights on all objects in the database. For instance, can the user
then execute a stored procedure in the database, or do you have to explicitly
go in and put an "X" in the execute permission checkbox next to the stored
procedure?
--
Message posted via http://www.sqlmonster.comRobert,
By dbo I assume that you assigned the user to the db_owner role. If you
have, then that person has rights to do absolutely anything, within the
bounds of that database.
If you don't want him to be able to do absolutely anything, then place him
in another role and grant that role the needed rights.
RLF
"Robert R via SQLMonster.com" <u3288@.uwe> wrote in message
news:571d2aa2f02d8@.uwe...
> If I set up a user with a database role of dbo, does that automatically
> give
> him dbo rights on all objects in the database. For instance, can the user
> then execute a stored procedure in the database, or do you have to
> explicitly
> go in and put an "X" in the execute permission checkbox next to the stored
> procedure?
> --
> Message posted via http://www.sqlmonster.com|||When granting the database owner role to the user, do you also need to put a
check mark (such as select, insert, update, delete) for access to a table, or
Exec for a stored procedure for the user to have those specified permissions
on the object, because when the user is granted the database owner role, all
the check boxes are blank.
Russell Fields wrote:
>Robert,
>By dbo I assume that you assigned the user to the db_owner role. If you
>have, then that person has rights to do absolutely anything, within the
>bounds of that database.
>If you don't want him to be able to do absolutely anything, then place him
>in another role and grant that role the needed rights.
>RLF
>> If I set up a user with a database role of dbo, does that automatically
>> give
>[quoted text clipped - 3 lines]
>> go in and put an "X" in the execute permission checkbox next to the stored
>> procedure?
--
Message posted via http://www.sqlmonster.com|||Making a user member of 'db_owner' role will ensure that the user 'has all
permissions in the database'. No need of to put additinal check mark for
select etc.
"Robert R via SQLMonster.com" wrote:
> When granting the database owner role to the user, do you also need to put a
> check mark (such as select, insert, update, delete) for access to a table, or
> Exec for a stored procedure for the user to have those specified permissions
> on the object, because when the user is granted the database owner role, all
> the check boxes are blank.
Saturday, February 25, 2012
Database Replication
Hi everybody
Can I replicate a database onto a seperate instance of SQL? I need to
replicate a database to a stand-alone machine with no connectivety to
anything. Does anyone know where I can find info on this?
Thanks for any help.
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forum...eneral/200509/1Perhaps in Books Online, under "Replication"? :-) And I guess you
don't really mean "no connectivity to anything", otherwise it would be
somewhat difficult to implement. Depending on what your goal is
(availability, disaster recovery, offline reporting etc.) you could
also look at log shipping, clustering, and of course backup/restore.
Replication is quite a specialized area, so if you have specific
questions about it, you'll probably get a better response in
microsoft.public.sqlserver.replication.
Simon|||Thanks Simon. I did check the Books Online ;-). All I need is our online
database on another stand-alone computer
that is not connected to any network (due to stupid security). I need this
for a whole bunch of testing on one of our apps. I think that the
backup/restore option would be better?
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forum...eneral/200509/1|||If you have no network connection, you'll need to copy the database to
a physical medium (DVD, tape, USB drive) anyway, so backup/restore
would be the only real option (you could detach/attach, but that would
mean taking the source offline).
Also see this article:
http://support.microsoft.com/defaul...kb;en-us;314546
Simon
Sunday, February 19, 2012
Database protection
Is there a way to prevent users from logging into an MSDE database instance?
I created an MSDE instance using a strong SA password but I was still able
to logging to the database using Windows Authentication.
The reason I don't want the user to see the database is because the database
structure that I am distributing (MSDE) is exactly the same as the one I
have online. If I let the user peek into my MSDE database they might find a
way to mess-up the database that is online. I just don't want to take the
risk.
Also, is stored procedure encryption easily bypassed if I logon as a
database administrator?
Thanks.
If you don=B4t want Windows Authentication, disable it:
http://support.microsoft.com/default...;EN-US;q285097
INF: How to Change the Default Login Authentication Mode to SQL While
Installing SQL Server 2000 Desktop Engine by Using Windows Installer
<snip>
Another way to change the security mode after installation is to stop
SQL Server and set the appropriate registry key for your installation:
Default instance:
HKLM\Software\Microsoft\MSSqlserver\MSSqlServer\Lo ginMode
Named instance:
HKLM\Software\Microsoft\Microsoft SQL Server\Instance
Name\MSSQLServer\LoginMode
to 2 for mixed-mode or 1 for integrated. (Integrated is the default
setup for the SQL Server 2000 Data Engine.)
</snip>
-URL---
HTH, Jens Suessmeyer.
|||Hello,
I notice you have posted the same question in our SQLServer newsgroup,
which I have already responded. So please check my answer there and if you
need any further assistance on this particular issue, please reply to me in
that thread so I can follow up with you in time. Thanks.
Sophie Guo
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
================================================== ===
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.
|||hi Jens,
Jens wrote:
> If you dont want Windows Authentication, disable it:
actually you cant disable Windows Authentication... you can disable standard
SQL Server authentication as you described, but not the contrary...
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
|||You sure right, I misunderstood the op.
Thanks.
|||In message <3ot19pF794d7U1@.individual.net>, Andrea Montanari
<andrea.sqlDMO@.virgilio.it> writes
>hi Jens,
>Jens wrote:
>actually you cant disable Windows Authentication... you can disable standard
>SQL Server authentication as you described, but not the contrary...
Correct, however ... you can remove the "BUILTIN\..." Windows users
from the allowed Logins under Security tab to effectively disable the
Windows Users from logging into that instance.
Andrew D. Newbould E-Mail: newsgroups@.NOSPAMzadsoft.com
ZAD Software Systems Web : www.zadsoft.com
|||hi Andrew,
Andrew D. Newbould wrote:
> ...
> Correct, however ... you can remove the "BUILTIN\..." Windows users
> from the allowed Logins under Security tab to effectively disable the
> Windows Users from logging into that instance.
yes, of course, but this has a nasty side effect on MSDE instance, where the
Agent will no longer be able to start up, where you can not use Enterprise
Manager to set up the Win login(s) running the SQL Server and SQL Server
Agent..
until sp4, you could use the http://support.microsoft.com/kb/283811/en-us to
provide the appropriate permissions for that account, but sp4 chaged
something I'm still trying to figure out...
I'm still trying troubleshooting it..
I tryed "propagating" file permissions to all sub folders as described, as
long as assigning registry permissions as
HKLM\Software\Microsoft\MSSQLServer\Setup (READ)
HKLM\Software\Microsoft\MSSQLServer\MSSQLServer (FULL CONTROL)
for the account running SQL Server and
HKLM\Software\Microsoft\MSSQLServer\SQLSERVERAGENT (FULL CONTROL)
HKLM\SOFTWARE\Microsoft\MSSQLServer\Client\SuperSo cketNetLib\LastConnect
(FULL CONTROL)
HKLM\Software\Description\Microsoft\Rpc\UuidTempor aryData (FULL CONTROL)
HKLM\Software\Microsoft\MSSQLServer\Setup (READ)
HKLM\Software\ODBC\ODBC.INI (FULL CONTROL)
for the account running the Agent...
making those accounts member of the local sysadmins WinNT role
it seems to work, but I'm not completely confident about that...
feedback is welcome :D:D
but I definitevely hope kb article 283811 gets updated..
Andrea Montanari
http://www.asql.biz/DbaMgr.shtm
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
I created an MSDE instance using a strong SA password but I was still able
to logging to the database using Windows Authentication.
The reason I don't want the user to see the database is because the database
structure that I am distributing (MSDE) is exactly the same as the one I
have online. If I let the user peek into my MSDE database they might find a
way to mess-up the database that is online. I just don't want to take the
risk.
Also, is stored procedure encryption easily bypassed if I logon as a
database administrator?
Thanks.
If you don=B4t want Windows Authentication, disable it:
http://support.microsoft.com/default...;EN-US;q285097
INF: How to Change the Default Login Authentication Mode to SQL While
Installing SQL Server 2000 Desktop Engine by Using Windows Installer
<snip>
Another way to change the security mode after installation is to stop
SQL Server and set the appropriate registry key for your installation:
Default instance:
HKLM\Software\Microsoft\MSSqlserver\MSSqlServer\Lo ginMode
Named instance:
HKLM\Software\Microsoft\Microsoft SQL Server\Instance
Name\MSSQLServer\LoginMode
to 2 for mixed-mode or 1 for integrated. (Integrated is the default
setup for the SQL Server 2000 Data Engine.)
</snip>
-URL---
HTH, Jens Suessmeyer.
|||Hello,
I notice you have posted the same question in our SQLServer newsgroup,
which I have already responded. So please check my answer there and if you
need any further assistance on this particular issue, please reply to me in
that thread so I can follow up with you in time. Thanks.
Sophie Guo
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
================================================== ===
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.
|||hi Jens,
Jens wrote:
> If you dont want Windows Authentication, disable it:
actually you cant disable Windows Authentication... you can disable standard
SQL Server authentication as you described, but not the contrary...
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
|||You sure right, I misunderstood the op.
Thanks.
|||In message <3ot19pF794d7U1@.individual.net>, Andrea Montanari
<andrea.sqlDMO@.virgilio.it> writes
>hi Jens,
>Jens wrote:
>actually you cant disable Windows Authentication... you can disable standard
>SQL Server authentication as you described, but not the contrary...
Correct, however ... you can remove the "BUILTIN\..." Windows users
from the allowed Logins under Security tab to effectively disable the
Windows Users from logging into that instance.
Andrew D. Newbould E-Mail: newsgroups@.NOSPAMzadsoft.com
ZAD Software Systems Web : www.zadsoft.com
|||hi Andrew,
Andrew D. Newbould wrote:
> ...
> Correct, however ... you can remove the "BUILTIN\..." Windows users
> from the allowed Logins under Security tab to effectively disable the
> Windows Users from logging into that instance.
yes, of course, but this has a nasty side effect on MSDE instance, where the
Agent will no longer be able to start up, where you can not use Enterprise
Manager to set up the Win login(s) running the SQL Server and SQL Server
Agent..
until sp4, you could use the http://support.microsoft.com/kb/283811/en-us to
provide the appropriate permissions for that account, but sp4 chaged
something I'm still trying to figure out...
I'm still trying troubleshooting it..
I tryed "propagating" file permissions to all sub folders as described, as
long as assigning registry permissions as
HKLM\Software\Microsoft\MSSQLServer\Setup (READ)
HKLM\Software\Microsoft\MSSQLServer\MSSQLServer (FULL CONTROL)
for the account running SQL Server and
HKLM\Software\Microsoft\MSSQLServer\SQLSERVERAGENT (FULL CONTROL)
HKLM\SOFTWARE\Microsoft\MSSQLServer\Client\SuperSo cketNetLib\LastConnect
(FULL CONTROL)
HKLM\Software\Description\Microsoft\Rpc\UuidTempor aryData (FULL CONTROL)
HKLM\Software\Microsoft\MSSQLServer\Setup (READ)
HKLM\Software\ODBC\ODBC.INI (FULL CONTROL)
for the account running the Agent...
making those accounts member of the local sysadmins WinNT role
it seems to work, but I'm not completely confident about that...
feedback is welcome :D:D
but I definitevely hope kb article 283811 gets updated..
Andrea Montanari
http://www.asql.biz/DbaMgr.shtm
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
Database protection
Is there a way to prevent users from logging into an MSDE database instance?
I created an MSDE instance using a strong SA password but I was still able
to logging to the database using Windows Authentication.
The reason I don't want the user to see the database is because the database
structure that I am distributing (MSDE) is exactly the same as the one I
have online. If I let the user peek into my MSDE database they might find a
way to mess-up the database that is online. I just don't want to take the
risk.
Also, is stored procedure encryption easily bypassed if I logon as a
database administrator?
Thanks.Rene
Create a new Login (DD) in SQL Server and and don't CRANT permission it to
the database. Now, when the user login with as DD he/she will not be able
access to the database
"Rene" <nospam@.nospam.com> wrote in message
news:uwzGIpbuFHA.2072@.TK2MSFTNGP14.phx.gbl...
> Is there a way to prevent users from logging into an MSDE database
> instance? I created an MSDE instance using a strong SA password but I was
> still able to logging to the database using Windows Authentication.
> The reason I don't want the user to see the database is because the
> database structure that I am distributing (MSDE) is exactly the same as
> the one I have online. If I let the user peek into my MSDE database they
> might find a way to mess-up the database that is online. I just don't want
> to take the risk.
> Also, is stored procedure encryption easily bypassed if I logon as a
> database administrator?
> Thanks.
>|||Thanks Uri
But if the user is logged on to Windows as an Administrator, doesn't this
user also has Admin right to the database by default? If this is the case,
even if I create a new database user it won't help because the person logged
into Windows as an administrator can automatically logging as "SA". Is this
the right?
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:eCCe1OcuFHA.3452@.TK2MSFTNGP14.phx.gbl...
> Rene
> Create a new Login (DD) in SQL Server and and don't CRANT permission it
> to the database. Now, when the user login with as DD he/she will not be
> able access to the database
>
>
> "Rene" <nospam@.nospam.com> wrote in message
> news:uwzGIpbuFHA.2072@.TK2MSFTNGP14.phx.gbl...
>|||Hello,
You can use sp_grantlogin to allow a Microsoft Windows NT user or group
account to connect to Microsoft SQL Server using Windows Authentication.
Use sp_denylogin to prevent a Microsoft Windows NT user or group from
connecting to Microsoft SQL Server. For example, you can run
sp_denylogin 'builtin\administrators'
to prevent from administrator to connect to SQL Server. However, make sure
you have the SA password and the authentication mode is mixed-mode before
you do so.
For more information, refer to the following articles:
http://msdn.microsoft.com/library/d...-us/tsqlref/ts_
sp_ga-gz_8dri.asp
http://msdn.microsoft.com/library/d...-us/tsqlref/ts_
sp_da-di_9jji.asp
I hope the information is helpful.
Sophie Guo
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
========================================
=============
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.|||Well, don't permit them to connect as Administrators if you want to
implement the policy.
Remove them from 'Windows Administrators' and add to the newly created Group
only for access to the specific database
"Rene" <nospam@.nospam.com> wrote in message
news:uKoLoucuFHA.3720@.TK2MSFTNGP14.phx.gbl...
> Thanks Uri
> But if the user is logged on to Windows as an Administrator, doesn't this
> user also has Admin right to the database by default? If this is the case,
> even if I create a new database user it won't help because the person
> logged into Windows as an administrator can automatically logging as "SA".
> Is this the right?
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:eCCe1OcuFHA.3452@.TK2MSFTNGP14.phx.gbl...
>|||Hi
Have you looked at sp_revokedbaccess?
John
"Rene" <nospam@.nospam.com> wrote in message
news:uwzGIpbuFHA.2072@.TK2MSFTNGP14.phx.gbl...
> Is there a way to prevent users from logging into an MSDE database
> instance? I created an MSDE instance using a strong SA password but I was
> still able to logging to the database using Windows Authentication.
> The reason I don't want the user to see the database is because the
> database structure that I am distributing (MSDE) is exactly the same as
> the one I have online. If I let the user peek into my MSDE database they
> might find a way to mess-up the database that is online. I just don't want
> to take the risk.
> Also, is stored procedure encryption easily bypassed if I logon as a
> database administrator?
> Thanks.
>|||Hi
If they are administrators and you are worried that they can damage your
system, then access to your new database is not your only problem!
John
"Rene" <nospam@.nospam.com> wrote in message
news:uKoLoucuFHA.3720@.TK2MSFTNGP14.phx.gbl...
> Thanks Uri
> But if the user is logged on to Windows as an Administrator, doesn't this
> user also has Admin right to the database by default? If this is the case,
> even if I create a new database user it won't help because the person
> logged into Windows as an administrator can automatically logging as "SA".
> Is this the right?
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:eCCe1OcuFHA.3452@.TK2MSFTNGP14.phx.gbl...
>
I created an MSDE instance using a strong SA password but I was still able
to logging to the database using Windows Authentication.
The reason I don't want the user to see the database is because the database
structure that I am distributing (MSDE) is exactly the same as the one I
have online. If I let the user peek into my MSDE database they might find a
way to mess-up the database that is online. I just don't want to take the
risk.
Also, is stored procedure encryption easily bypassed if I logon as a
database administrator?
Thanks.Rene
Create a new Login (DD) in SQL Server and and don't CRANT permission it to
the database. Now, when the user login with as DD he/she will not be able
access to the database
"Rene" <nospam@.nospam.com> wrote in message
news:uwzGIpbuFHA.2072@.TK2MSFTNGP14.phx.gbl...
> Is there a way to prevent users from logging into an MSDE database
> instance? I created an MSDE instance using a strong SA password but I was
> still able to logging to the database using Windows Authentication.
> The reason I don't want the user to see the database is because the
> database structure that I am distributing (MSDE) is exactly the same as
> the one I have online. If I let the user peek into my MSDE database they
> might find a way to mess-up the database that is online. I just don't want
> to take the risk.
> Also, is stored procedure encryption easily bypassed if I logon as a
> database administrator?
> Thanks.
>|||Thanks Uri
But if the user is logged on to Windows as an Administrator, doesn't this
user also has Admin right to the database by default? If this is the case,
even if I create a new database user it won't help because the person logged
into Windows as an administrator can automatically logging as "SA". Is this
the right?
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:eCCe1OcuFHA.3452@.TK2MSFTNGP14.phx.gbl...
> Rene
> Create a new Login (DD) in SQL Server and and don't CRANT permission it
> to the database. Now, when the user login with as DD he/she will not be
> able access to the database
>
>
> "Rene" <nospam@.nospam.com> wrote in message
> news:uwzGIpbuFHA.2072@.TK2MSFTNGP14.phx.gbl...
>|||Hello,
You can use sp_grantlogin to allow a Microsoft Windows NT user or group
account to connect to Microsoft SQL Server using Windows Authentication.
Use sp_denylogin to prevent a Microsoft Windows NT user or group from
connecting to Microsoft SQL Server. For example, you can run
sp_denylogin 'builtin\administrators'
to prevent from administrator to connect to SQL Server. However, make sure
you have the SA password and the authentication mode is mixed-mode before
you do so.
For more information, refer to the following articles:
http://msdn.microsoft.com/library/d...-us/tsqlref/ts_
sp_ga-gz_8dri.asp
http://msdn.microsoft.com/library/d...-us/tsqlref/ts_
sp_da-di_9jji.asp
I hope the information is helpful.
Sophie Guo
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
========================================
=============
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.|||Well, don't permit them to connect as Administrators if you want to
implement the policy.
Remove them from 'Windows Administrators' and add to the newly created Group
only for access to the specific database
"Rene" <nospam@.nospam.com> wrote in message
news:uKoLoucuFHA.3720@.TK2MSFTNGP14.phx.gbl...
> Thanks Uri
> But if the user is logged on to Windows as an Administrator, doesn't this
> user also has Admin right to the database by default? If this is the case,
> even if I create a new database user it won't help because the person
> logged into Windows as an administrator can automatically logging as "SA".
> Is this the right?
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:eCCe1OcuFHA.3452@.TK2MSFTNGP14.phx.gbl...
>|||Hi
Have you looked at sp_revokedbaccess?
John
"Rene" <nospam@.nospam.com> wrote in message
news:uwzGIpbuFHA.2072@.TK2MSFTNGP14.phx.gbl...
> Is there a way to prevent users from logging into an MSDE database
> instance? I created an MSDE instance using a strong SA password but I was
> still able to logging to the database using Windows Authentication.
> The reason I don't want the user to see the database is because the
> database structure that I am distributing (MSDE) is exactly the same as
> the one I have online. If I let the user peek into my MSDE database they
> might find a way to mess-up the database that is online. I just don't want
> to take the risk.
> Also, is stored procedure encryption easily bypassed if I logon as a
> database administrator?
> Thanks.
>|||Hi
If they are administrators and you are worried that they can damage your
system, then access to your new database is not your only problem!
John
"Rene" <nospam@.nospam.com> wrote in message
news:uKoLoucuFHA.3720@.TK2MSFTNGP14.phx.gbl...
> Thanks Uri
> But if the user is logged on to Windows as an Administrator, doesn't this
> user also has Admin right to the database by default? If this is the case,
> even if I create a new database user it won't help because the person
> logged into Windows as an administrator can automatically logging as "SA".
> Is this the right?
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:eCCe1OcuFHA.3452@.TK2MSFTNGP14.phx.gbl...
>
Database properties
Hi.
I am trying to look for ways on how to get all the databse properties under each instance.
say I want to view the size, recovery model, physical location, etc.
The sys.database_files is only applicable per database. Is there a query, or a table in master database to get these or store these values?
thanks...
You can query the sys.databases and sys.master_files to get this information on a per instance basis. You will need to query each instance seperatly.|||Thanks for that!
Subscribe to:
Posts (Atom)