Database special users

What are the sys and information_schema special database users used for.

Hi!

sys and information_schema are special database users who has "public" access rights(by default) to access "INFORMATION_SCHEMA" and "SYS" system views, like: INFORMATION_SCHEMA.TABLES, INFORMATION_SCHEMA.COLUMNS, etc...

Note: And you cannot delete these users.

Thanks & Regards,

Kiran.Y

database size

Hi all, I have an asp application that allows users to upload files and
these are being stored in a sql database. The database size is growing
relatively fast (at about 1 GB) now. I know the recommended way of storing
uploaded files is on the file system but I chose the database for several
reasons including security and easy of backup since all data is in 1 central
location. My question is does the size of the database effect the overall
performance of the sql server as far as that db is concerned? I have many
other tables in that database being used in various other operations daily.
Would there be any benefit in maybe moving the uploaded file tables to a
different database?
much thanks in advance!Of course you could have some impact on performance. You regular tables
could become fragmented. But you could still have the uploaded files in the
database, just put them to a separate data file (a database can have many
data files). If the file would be on a separate disk, this would be even
better. Otherwise make sure that the primary data file (with regular tables)
is big enough, so it will not expand, otherwise you could get disk
fragmentation.
Dejan Sarka, SQL Server MVP
Associate Mentor
Solid Quality Learning
More than just Training
www.SolidQualityLearning.com
"RP" <rp@.nospam.com> wrote in message
news:%23dc0xH7IEHA.3964@.TK2MSFTNGP10.phx.gbl...
> Hi all, I have an asp application that allows users to upload files and
> these are being stored in a sql database. The database size is growing
> relatively fast (at about 1 GB) now. I know the recommended way of storing
> uploaded files is on the file system but I chose the database for several
> reasons including security and easy of backup since all data is in 1
central
> location. My question is does the size of the database effect the overall
> performance of the sql server as far as that db is concerned? I have many
> other tables in that database being used in various other operations
daily.
> Would there be any benefit in maybe moving the uploaded file tables to a
> different database?
> much thanks in advance!
>|||Dejan, thank you for your reply. I like the idea of multiple data files for
a database. How would I go about setting this up? Can I specify certain
tables to certain data files? Right now the database has 1 data and 1 log
file and each one is set to grow automatically by 10%. Would multiple data
files affect my backup settings? I have the server setup to backup the
database on a daily basis. Would it backup each data file or just one?
Your help is much appreciated.
thanks a lot!
"Dejan Sarka" <dejan_please_reply_to_newsgroups.sarka@.avtenta.si> wrote in
message news:OZzhCTGJEHA.628@.TK2MSFTNGP11.phx.gbl...
> Of course you could have some impact on performance. You regular tables
> could become fragmented. But you could still have the uploaded files in
the
> database, just put them to a separate data file (a database can have many
> data files). If the file would be on a separate disk, this would be even
> better. Otherwise make sure that the primary data file (with regular
tables)
> is big enough, so it will not expand, otherwise you could get disk
> fragmentation.
> --
> Dejan Sarka, SQL Server MVP
> Associate Mentor
> Solid Quality Learning
> More than just Training
> www.SolidQualityLearning.com
> "RP" <rp@.nospam.com> wrote in message
> news:%23dc0xH7IEHA.3964@.TK2MSFTNGP10.phx.gbl...
storing[vbcol=seagreen]
several[vbcol=seagreen]
> central
overall[vbcol=seagreen]
many[vbcol=seagreen]
> daily.
>|||>
> I like the idea of multiple data files for
> a database. How would I go about setting this up?
--
You can use the ALTER DATABASE with the ADD FILEGROUP option.

> Can I specify certain tables to certain data files?
--
You can use the CREATE TABLE with ON <filegroup> clause to specify which
filegroup the table will be stored.

> Would multiple data files affect my backup settings?
--
You can backup the entire database or certain files or filegroups only in
that database.
For more details on the above commands, please consult your SQL Server
Books online.
Hope this helps,
Eric Crdenas
SQL Server senior support professionalsql

database size

Hi all, I have an asp application that allows users to upload files and
these are being stored in a sql database. The database size is growing
relatively fast (at about 1 GB) now. I know the recommended way of storing
uploaded files is on the file system but I chose the database for several
reasons including security and easy of backup since all data is in 1 central
location. My question is does the size of the database effect the overall
performance of the sql server as far as that db is concerned? I have many
other tables in that database being used in various other operations daily.
Would there be any benefit in maybe moving the uploaded file tables to a
different database?
much thanks in advance!Of course you could have some impact on performance. You regular tables
could become fragmented. But you could still have the uploaded files in the
database, just put them to a separate data file (a database can have many
data files). If the file would be on a separate disk, this would be even
better. Otherwise make sure that the primary data file (with regular tables)
is big enough, so it will not expand, otherwise you could get disk
fragmentation.
--
Dejan Sarka, SQL Server MVP
Associate Mentor
Solid Quality Learning
More than just Training
www.SolidQualityLearning.com
"RP" <rp@.nospam.com> wrote in message
news:%23dc0xH7IEHA.3964@.TK2MSFTNGP10.phx.gbl...
> Hi all, I have an asp application that allows users to upload files and
> these are being stored in a sql database. The database size is growing
> relatively fast (at about 1 GB) now. I know the recommended way of storing
> uploaded files is on the file system but I chose the database for several
> reasons including security and easy of backup since all data is in 1
central
> location. My question is does the size of the database effect the overall
> performance of the sql server as far as that db is concerned? I have many
> other tables in that database being used in various other operations
daily.
> Would there be any benefit in maybe moving the uploaded file tables to a
> different database?
> much thanks in advance!
>|||Dejan, thank you for your reply. I like the idea of multiple data files for
a database. How would I go about setting this up? Can I specify certain
tables to certain data files? Right now the database has 1 data and 1 log
file and each one is set to grow automatically by 10%. Would multiple data
files affect my backup settings? I have the server setup to backup the
database on a daily basis. Would it backup each data file or just one?
Your help is much appreciated.
thanks a lot!
"Dejan Sarka" <dejan_please_reply_to_newsgroups.sarka@.avtenta.si> wrote in
message news:OZzhCTGJEHA.628@.TK2MSFTNGP11.phx.gbl...
> Of course you could have some impact on performance. You regular tables
> could become fragmented. But you could still have the uploaded files in
the
> database, just put them to a separate data file (a database can have many
> data files). If the file would be on a separate disk, this would be even
> better. Otherwise make sure that the primary data file (with regular
tables)
> is big enough, so it will not expand, otherwise you could get disk
> fragmentation.
> --
> Dejan Sarka, SQL Server MVP
> Associate Mentor
> Solid Quality Learning
> More than just Training
> www.SolidQualityLearning.com
> "RP" <rp@.nospam.com> wrote in message
> news:%23dc0xH7IEHA.3964@.TK2MSFTNGP10.phx.gbl...
> > Hi all, I have an asp application that allows users to upload files and
> > these are being stored in a sql database. The database size is growing
> > relatively fast (at about 1 GB) now. I know the recommended way of
storing
> > uploaded files is on the file system but I chose the database for
several
> > reasons including security and easy of backup since all data is in 1
> central
> > location. My question is does the size of the database effect the
overall
> > performance of the sql server as far as that db is concerned? I have
many
> > other tables in that database being used in various other operations
> daily.
> > Would there be any benefit in maybe moving the uploaded file tables to a
> > different database?
> >
> > much thanks in advance!
> >
> >
>|||>
> I like the idea of multiple data files for
> a database. How would I go about setting this up?
--
You can use the ALTER DATABASE with the ADD FILEGROUP option.
> Can I specify certain tables to certain data files?
--
You can use the CREATE TABLE with ON <filegroup> clause to specify which
filegroup the table will be stored.
> Would multiple data files affect my backup settings?
--
You can backup the entire database or certain files or filegroups only in
that database.
For more details on the above commands, please consult your SQL Server
Books online.
Hope this helps,
--
Eric Cárdenas
SQL Server senior support professional

database size

Hi all, I have an asp application that allows users to upload files and
these are being stored in a sql database. The database size is growing
relatively fast (at about 1 GB) now. I know the recommended way of storing
uploaded files is on the file system but I chose the database for several
reasons including security and easy of backup since all data is in 1 central
location. My question is does the size of the database effect the overall
performance of the sql server as far as that db is concerned? I have many
other tables in that database being used in various other operations daily.
Would there be any benefit in maybe moving the uploaded file tables to a
different database?
much thanks in advance!
Of course you could have some impact on performance. You regular tables
could become fragmented. But you could still have the uploaded files in the
database, just put them to a separate data file (a database can have many
data files). If the file would be on a separate disk, this would be even
better. Otherwise make sure that the primary data file (with regular tables)
is big enough, so it will not expand, otherwise you could get disk
fragmentation.
Dejan Sarka, SQL Server MVP
Associate Mentor
Solid Quality Learning
More than just Training
www.SolidQualityLearning.com
"RP" <rp@.nospam.com> wrote in message
news:%23dc0xH7IEHA.3964@.TK2MSFTNGP10.phx.gbl...
> Hi all, I have an asp application that allows users to upload files and
> these are being stored in a sql database. The database size is growing
> relatively fast (at about 1 GB) now. I know the recommended way of storing
> uploaded files is on the file system but I chose the database for several
> reasons including security and easy of backup since all data is in 1
central
> location. My question is does the size of the database effect the overall
> performance of the sql server as far as that db is concerned? I have many
> other tables in that database being used in various other operations
daily.
> Would there be any benefit in maybe moving the uploaded file tables to a
> different database?
> much thanks in advance!
>
|||Dejan, thank you for your reply. I like the idea of multiple data files for
a database. How would I go about setting this up? Can I specify certain
tables to certain data files? Right now the database has 1 data and 1 log
file and each one is set to grow automatically by 10%. Would multiple data
files affect my backup settings? I have the server setup to backup the
database on a daily basis. Would it backup each data file or just one?
Your help is much appreciated.
thanks a lot!
"Dejan Sarka" <dejan_please_reply_to_newsgroups.sarka@.avtenta.si > wrote in
message news:OZzhCTGJEHA.628@.TK2MSFTNGP11.phx.gbl...
> Of course you could have some impact on performance. You regular tables
> could become fragmented. But you could still have the uploaded files in
the
> database, just put them to a separate data file (a database can have many
> data files). If the file would be on a separate disk, this would be even
> better. Otherwise make sure that the primary data file (with regular
tables)[vbcol=seagreen]
> is big enough, so it will not expand, otherwise you could get disk
> fragmentation.
> --
> Dejan Sarka, SQL Server MVP
> Associate Mentor
> Solid Quality Learning
> More than just Training
> www.SolidQualityLearning.com
> "RP" <rp@.nospam.com> wrote in message
> news:%23dc0xH7IEHA.3964@.TK2MSFTNGP10.phx.gbl...
storing[vbcol=seagreen]
several[vbcol=seagreen]
> central
overall[vbcol=seagreen]
many
> daily.
>
|||>
> I like the idea of multiple data files for
> a database. How would I go about setting this up?
You can use the ALTER DATABASE with the ADD FILEGROUP option.

> Can I specify certain tables to certain data files?
You can use the CREATE TABLE with ON <filegroup> clause to specify which
filegroup the table will be stored.

> Would multiple data files affect my backup settings?
You can backup the entire database or certain files or filegroups only in
that database.
For more details on the above commands, please consult your SQL Server
Books online.
Hope this helps,
Eric Crdenas
SQL Server senior support professional
sql

Database Role/User Query

Anyone have a tsql query that will give me a listing of database roles and their users already put together?try sp_helpuser with no arguments.

Database Restore SQL 2005

Hi,
How do I allow a user permission to restore a database? Users can backup
databases but can't restore them? User is dbo on the database.
Same permisssions seemed to work fine in 2000.
--
Bill Ross
Denver, COIt probably worked before because the db already existed on the server.
From BooksOnLine under RESTORE DATABASE:
If the database being restored does not exist, the user must have CREATE
DATABASE permissions to be able to execute RESTORE. If the database exists,
RESTORE permissions default to members of the sysadmin and dbcreator fixed
server roles and the owner (dbo) of the database (for the FROM
DATABASE_SNAPSHOT option, the database always exists).
Andrew J. Kelly SQL MVP
"Bill Ross" <BillRoss@.discussions.microsoft.com> wrote in message
news:CB83664C-DEEF-40B0-82EF-7A688206DCE2@.microsoft.com...
> Hi,
> How do I allow a user permission to restore a database? Users can backup
> databases but can't restore them? User is dbo on the database.
> Same permisssions seemed to work fine in 2000.
> --
> Bill Ross
> Denver, CO|||Hi Andrew,
Thanks for the info. The user is dbo on the database and the database
exists? Do users need to be part of the dbcreator role? Does that mean tha
t
a user cannot restore a damaged db unless they have rights to create any db?
Most confusing.
Bill Ross
Denver, CO
"Andrew J. Kelly" wrote:

> It probably worked before because the db already existed on the server.
> From BooksOnLine under RESTORE DATABASE:
> If the database being restored does not exist, the user must have CREATE
> DATABASE permissions to be able to execute RESTORE. If the database exists
,
> RESTORE permissions default to members of the sysadmin and dbcreator fixed
> server roles and the owner (dbo) of the database (for the FROM
> DATABASE_SNAPSHOT option, the database always exists).
>
> --
> Andrew J. Kelly SQL MVP
> "Bill Ross" <BillRoss@.discussions.microsoft.com> wrote in message
> news:CB83664C-DEEF-40B0-82EF-7A688206DCE2@.microsoft.com...
>
>|||Bill
Do users need to be part of the dbcreator role?
Yes, or you can grant CREATE DATABASE permissions to be able to execute
RESTORE as Andrew pointed.
Does that mean that
> a user cannot restore a damaged db unless they have rights to create any
> db?
If the database does not exist they cannot unless they got CREATE DATABASE
permissions or members of dbcreator role
If the database is corrupt the user must be a memeber of dbcreator role
,more over he/she should has access to msdb dataabse in order to write a
history of backups/restores
"Bill Ross" <BillRoss@.discussions.microsoft.com> wrote in message
news:866D621A-EE05-48B9-9252-A603AD7A0154@.microsoft.com...[vbcol=seagreen]
> Hi Andrew,
> Thanks for the info. The user is dbo on the database and the database
> exists? Do users need to be part of the dbcreator role? Does that mean
> that
> a user cannot restore a damaged db unless they have rights to create any
> db?
> Most confusing.
> --
> Bill Ross
> Denver, CO
>
> "Andrew J. Kelly" wrote:
>

database restore from production to development

Hi,
I am in the process of overlaying our development data
with production backup. The users are different in prod as
compared with dev. My concern is if I restore production
copy on to dev, it might overwrite the sysusers info in
dev. I don't want to a) add prod users onto dev and b)
remove existing users in dev after the restore.
How do I keep my sysusers info intact ?
Currently I am thinking of
a) Get all users from dev and keep it aside
b) Restore the production onto dev
c) Delete all users in dev
d) Add users from (a) to dev
Will this work ? are there any other ways ?
TIA
MohanHave you looked at sp_change_users_login?
--
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Mo" <anonymous@.discussions.microsoft.com> wrote in message
news:d7c301c3ef32$7c071cb0$a501280a@.phx.gbl...
> Hi,
> I am in the process of overlaying our development data
> with production backup. The users are different in prod as
> compared with dev. My concern is if I restore production
> copy on to dev, it might overwrite the sysusers info in
> dev. I don't want to a) add prod users onto dev and b)
> remove existing users in dev after the restore.
> How do I keep my sysusers info intact ?
> Currently I am thinking of
> a) Get all users from dev and keep it aside
> b) Restore the production onto dev
> c) Delete all users in dev
> d) Add users from (a) to dev
> Will this work ? are there any other ways ?
> TIA
> Mohan
>|||Mo,
A way would be scripting out all database users on the development server
and run this script every time you restore the production backup. You can
create a sql agent job that restores the database and then runs this script
as an additional step.
--
Carlos E. Rojas
SQL Server MVP
Co-Author SQL Server 2000 Programming by Example
"Mo" <anonymous@.discussions.microsoft.com> wrote in message
news:d7c301c3ef32$7c071cb0$a501280a@.phx.gbl...
> Hi,
> I am in the process of overlaying our development data
> with production backup. The users are different in prod as
> compared with dev. My concern is if I restore production
> copy on to dev, it might overwrite the sysusers info in
> dev. I don't want to a) add prod users onto dev and b)
> remove existing users in dev after the restore.
> How do I keep my sysusers info intact ?
> Currently I am thinking of
> a) Get all users from dev and keep it aside
> b) Restore the production onto dev
> c) Delete all users in dev
> d) Add users from (a) to dev
> Will this work ? are there any other ways ?
> TIA
> Mohan
>

database restore from production to development

Hi,
I am in the process of overlaying our development data
with production backup. The users are different in prod as
compared with dev. My concern is if I restore production
copy on to dev, it might overwrite the sysusers info in
dev. I don't want to a) add prod users onto dev and b)
remove existing users in dev after the restore.
How do I keep my sysusers info intact ?
Currently I am thinking of
a) Get all users from dev and keep it aside
b) Restore the production onto dev
c) Delete all users in dev
d) Add users from (a) to dev
Will this work ? are there any other ways ?
TIA
MohanHave you looked at sp_change_users_login?
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Mo" <anonymous@.discussions.microsoft.com> wrote in message
news:d7c301c3ef32$7c071cb0$a501280a@.phx.gbl...
> Hi,
> I am in the process of overlaying our development data
> with production backup. The users are different in prod as
> compared with dev. My concern is if I restore production
> copy on to dev, it might overwrite the sysusers info in
> dev. I don't want to a) add prod users onto dev and b)
> remove existing users in dev after the restore.
> How do I keep my sysusers info intact ?
> Currently I am thinking of
> a) Get all users from dev and keep it aside
> b) Restore the production onto dev
> c) Delete all users in dev
> d) Add users from (a) to dev
> Will this work ? are there any other ways ?
> TIA
> Mohan
>|||Mo,
A way would be scripting out all database users on the development server
and run this script every time you restore the production backup. You can
create a sql agent job that restores the database and then runs this script
as an additional step.
--
Carlos E. Rojas
SQL Server MVP
Co-Author SQL Server 2000 programming by Example
"Mo" <anonymous@.discussions.microsoft.com> wrote in message
news:d7c301c3ef32$7c071cb0$a501280a@.phx.gbl...
> Hi,
> I am in the process of overlaying our development data
> with production backup. The users are different in prod as
> compared with dev. My concern is if I restore production
> copy on to dev, it might overwrite the sysusers info in
> dev. I don't want to a) add prod users onto dev and b)
> remove existing users in dev after the restore.
> How do I keep my sysusers info intact ?
> Currently I am thinking of
> a) Get all users from dev and keep it aside
> b) Restore the production onto dev
> c) Delete all users in dev
> d) Add users from (a) to dev
> Will this work ? are there any other ways ?
> TIA
> Mohan
>

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

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...
>

Database Properties

Is there an "sp_" already inckuded in SQL servere 2000 that will return some
properties of the database incouding users, (rights maybe?)?Check out the sp_help* stored procedures in Books Online.
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"RichieRich" <RichieRich@.discussions.microsoft.com> wrote in message
news:586AD0CF-F4FE-405C-A8AB-7561BC2034E1@.microsoft.com...
> Is there an "sp_" already inckuded in SQL servere 2000 that will return
some
> properties of the database incouding users, (rights maybe?)?

Database ownership

Hi
Can an NT GROUP own a database in SQL2000 ? I'm trying to have users in the group create objects with dbo owner ship by default.
However sp_changedbowner does not seem to recogise the group as a login even though sp_helplogins lists it .
Thanksmay be a bug with sp_changedbowner, when done through EM it works.|||Originally posted by Paul Young
may be a bug with sp_changedbowner, when done through EM it works.

Thanks Paul.

I can't seem to see where to do this in EM. Do you mean by adding the group to the dbo role?|||yup!
under Security, right click on the group, select Properties, then Database Access Tab, select the db, and click on the db_owner.|||Originally posted by Paul Young
yup!
under Security, right click on the group, select Properties, then Database Access Tab, select the db, and click on the db_owner.

Thanks again

However I'm getting tables owned by individual nt users which cannot be seen by other members of that group (i'm testing with users
connecting via query analyzer using nt authentication.

What I'm looking for is to be able to get the database owned by
the nt group so that any table created by the users are owned by dbo
and not the individual users, or at least have it so that all users of the group can see and manipulate the tables.

Any ideas?|||user tables are always use instead of dbo tables. Also, don't quote me on this, I think if you are aliased to dbo and alter a uesr table the table remains under the users'd id, it's only when you drop and create that the user identity changes.

Can you move all the user objects to a holding db and try your tests again?|||Originally posted by Paul Young
user tables are always use instead of dbo tables. Also, don't quote me on this, I think if you are aliased to dbo and alter a uesr table the table remains under the users'd id, it's only when you drop and create that the user identity changes.

Can you move all the user objects to a holding db and try your tests again?

Thanks for your patience. When a sql login owns a database then
all tables created are dbo.table rather than sqllogin.table

This is what I'm trying to achieve with the NT group .

When I create a table under an nt user which is in that nt group (whose sql login has dbo access to the database) the table is created as ntuser.table , and that nt user appears as a user in the database (where it didn't appear before ).

I tried to run the sp_addalias to get around this too but this also gave me an error as if it didn't recognise the nt group sql login.

I think I'm on the right track its just annoying that I can't find any real information about this scenario. My other option is to ensure that all tables are explicitly created with dbo. but not all 3rd party applications are so forgiving .

Thanks again for your help Paul

database owner with restrictions

I have group of users that I would like to have the ability to create tables
as dbo owners but would deny them insert,update and delete roles in some
tables.
It that scenario possible? If it is how?
Thanks in advance,
Tony
icwgroupdb_ddladmin role will allow the user to do DDL operations (i.e.
create/delete/alter <obj>). See this for other predefined roles:
http://msdn.microsoft.com/library/en-us/adminsql/ad_security_6ndx.asp
-oj
"tony-icwgroup" <tonyicwgroup@.discussions.microsoft.com> wrote in message
news:CA57FF63-5ABE-4B7C-8D3F-21150CE2BC56@.microsoft.com...
>I have group of users that I would like to have the ability to create
>tables
> as dbo owners but would deny them insert,update and delete roles in some
> tables.
> It that scenario possible? If it is how?
> Thanks in advance,
> Tony
> icwgroup

database owner with restrictions

I have group of users that I would like to have the ability to create tables
as dbo owners but would deny them insert,update and delete roles in some
tables.
It that scenario possible? If it is how?
Thanks in advance,
Tony
icwgroup
db_ddladmin role will allow the user to do DDL operations (i.e.
create/delete/alter <obj>). See this for other predefined roles:
http://msdn.microsoft.com/library/en...urity_6ndx.asp
-oj
"tony-icwgroup" <tonyicwgroup@.discussions.microsoft.com> wrote in message
news:CA57FF63-5ABE-4B7C-8D3F-21150CE2BC56@.microsoft.com...
>I have group of users that I would like to have the ability to create
>tables
> as dbo owners but would deny them insert,update and delete roles in some
> tables.
> It that scenario possible? If it is how?
> Thanks in advance,
> Tony
> icwgroup

database owner with restrictions

I have group of users that I would like to have the ability to create tables
as dbo owners but would deny them insert,update and delete roles in some
tables.
It that scenario possible? If it is how?
Thanks in advance,
Tony
icwgroupdb_ddladmin role will allow the user to do DDL operations (i.e.
create/delete/alter <obj> ). See this for other predefined roles:
http://msdn.microsoft.com/library/e...curity_6ndx.asp
-oj
"tony-icwgroup" <tonyicwgroup@.discussions.microsoft.com> wrote in message
news:CA57FF63-5ABE-4B7C-8D3F-21150CE2BC56@.microsoft.com...
>I have group of users that I would like to have the ability to create
>tables
> as dbo owners but would deny them insert,update and delete roles in some
> tables.
> It that scenario possible? If it is how?
> Thanks in advance,
> Tony
> icwgroup

Database or Server?

I am creating a site where users can create blogs and upload photos etc...I
just realized that my hosting account with GoDaddy has a 100 MB limit on the
SQL Server database...due to that I decided to store all photos on the serve
r
and just store the file path in the database...is that a good idea?
Also, what should I do with my blog entries? Should I store them as Text in
the database or should I create them as files on the server similar to the
photos?
Does anybody know if there are any hosting plans with a higher database
limit (for .NET 2.0 and SQL Server 2000/2005)? Or do I need to get a
dedicated server for that?>I am creating a site where users can create blogs and upload photos etc...I
> just realized that my hosting account with GoDaddy has a 100 MB limit on
> the
> SQL Server database...due to that I decided to store all photos on the
> server
> and just store the file path in the database...is that a good idea?
YES! Disk space for files is cheaper and much more efficient, with the only
real cost being losing the ability to backup all the files in the same
"transaction" so to speak as backing up the data.

> Also, what should I do with my blog entries? Should I store them as Text
> in
> the database or should I create them as files on the server similar to the
> photos?
Well, for 100 MB, let's say the average blog entry is 5K, that's 200
entries. So, do you expect to have 100 users with one blog entry each per
day? If so, you're out of space after 2 days. And that's without taking
away from the space required by the entries for photos, comments, member
information etc.

> Does anybody know if there are any hosting plans with a higher database
> limit (for .NET 2.0 and SQL Server 2000/2005)? Or do I need to get a
> dedicated server for that?
If you're sharing a SQL Server database server with other clients, there is
always going to be a threshold. It sounds like you need a more
comprehensive solution.|||Hi Swami,
I'd go with photos on the server and blog entries in the database. I don't
know about hosting plans.
"Swami" wrote:

> I am creating a site where users can create blogs and upload photos etc...
I
> just realized that my hosting account with GoDaddy has a 100 MB limit on t
he
> SQL Server database...due to that I decided to store all photos on the ser
ver
> and just store the file path in the database...is that a good idea?
> Also, what should I do with my blog entries? Should I store them as Text
in
> the database or should I create them as files on the server similar to the
> photos?
> Does anybody know if there are any hosting plans with a higher database
> limit (for .NET 2.0 and SQL Server 2000/2005)? Or do I need to get a
> dedicated server for that?
>|||> Well, for 100 MB, let's say the average blog entry is 5K, that's 200
> entries.
Is this right?
"Aaron Bertrand [SQL Server MVP]" wrote:

> YES! Disk space for files is cheaper and much more efficient, with the on
ly
> real cost being losing the ability to backup all the files in the same
> "transaction" so to speak as backing up the data.
>
> Well, for 100 MB, let's say the average blog entry is 5K, that's 200
> entries. So, do you expect to have 100 users with one blog entry each per
> day? If so, you're out of space after 2 days. And that's without taking
> away from the space required by the entries for photos, comments, member
> information etc.
>
> If you're sharing a SQL Server database server with other clients, there i
s
> always going to be a threshold. It sounds like you need a more
> comprehensive solution.
>
>|||Thanks! What types of comprehensive solutions are available? I am pretty
new to the hosting world, and I am not sure what my options are...
"Aaron Bertrand [SQL Server MVP]" wrote:

> YES! Disk space for files is cheaper and much more efficient, with the on
ly
> real cost being losing the ability to backup all the files in the same
> "transaction" so to speak as backing up the data.
>
> Well, for 100 MB, let's say the average blog entry is 5K, that's 200
> entries. So, do you expect to have 100 users with one blog entry each per
> day? If so, you're out of space after 2 days. And that's without taking
> away from the space required by the entries for photos, comments, member
> information etc.
>
> If you're sharing a SQL Server database server with other clients, there i
s
> always going to be a threshold. It sounds like you need a more
> comprehensive solution.
>
>|||>> Well, for 100 MB, let's say the average blog entry is 5K, that's 200
> Is this right?
You're right, my decimal places were off, I did 1 MB instead of 10 MB. My
point remains intact (you get a longer lead time to find a bigger area for
your data).|||On Tue, 25 Apr 2006 09:53:01 -0700, Ryan Randall
<RyanRandall@.discussions.microsoft.com> wrote:

>Is this right?
No, but what are a few decimal places among friends? Just shift it
over two places, 20000 entries by that rough calculation.
Roy Harvey
Beacon Falls, CT|||200 * 5K = 1000K = 1MB
so you would have space for 200,000 blog entries of 5K each.
On a completely unrelated note:
In 1870 Dr. E. von Wolf published a study on spinach and misplaced the
decimal point regarding the iron content. As a result he reported that the
content was 10 times higher than it actually is. Thw mistake was not found
and corrected until 1937.
"Ryan Randall" <RyanRandall@.discussions.microsoft.com> wrote in message
news:8768F602-CEEB-444E-84B8-003696D88874@.microsoft.com...
> Is this right?
>
> "Aaron Bertrand [SQL Server MVP]" wrote:
>
etc...I
on
only
Text
the
per
taking
database
is|||And there I go, misplacing my own decimal point. How ironic is that?
Should be 20,000, not 200,000.
"Jim Underwood" <james.underwoodATfallonclinic.com> wrote in message
news:%23pvbTqIaGHA.3736@.TK2MSFTNGP04.phx.gbl...
> 200 * 5K = 1000K = 1MB
> so you would have space for 200,000 blog entries of 5K each.
> On a completely unrelated note:
> In 1870 Dr. E. von Wolf published a study on spinach and misplaced the
> decimal point regarding the iron content. As a result he reported that
the
> content was 10 times higher than it actually is. Thw mistake was not
found
> and corrected until 1937.
> "Ryan Randall" <RyanRandall@.discussions.microsoft.com> wrote in message
> news:8768F602-CEEB-444E-84B8-003696D88874@.microsoft.com...
> etc...I
limit
> on
the
the
> only
> Text
to
> the
> per
> taking
member
> database
there
> is
>|||Yes. And surely you can see that either number is not all that scalable; if
the OP is building a blog site that has any chance of being even remotely
successful, they will quickly be sing more space.

> And there I go, misplacing my own decimal point. How ironic is that?
> Should be 20,000, not 200,000.