Re: SQL Server Express

First, it's not a case of SQL Express not including documentation, it's a
case of the documentation being a separate download:
http://www.microsoft.com/downloads/details.aspx?FamilyID=be6a2c5d-00df-4220-b133-29c1e0b6585f&DisplayLang=en
To allow your SQL Server authenticated users to create tables, you have to
GRANT them permissions to do so with the GRANT command. If all you want the
user to do is create tables, you would specify:
GRANT CREATE DATABASE on DATABASE::xxx to SAM
If you want the user to do anything within that database you could try:
GRANT CONTROL on DATABASE::xxx to SAM
The Windows users have rights because they are administrators. There's not
really a good way to block administrator access without making your database
pretty hard to administer.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm

"Ken Allen" <kendrhyd@xxxxxxxxxxxx> wrote in message
news:edp$n2zKGHA.1424@xxxxxxxxxxxxxxxxxxxxxxx
Andrea Montanari wrote:
hi Ken,
Ken Allen wrote:

<snipped />
you can get the full documentatiom, BOL , from
http://www.microsoft.com/downloads/details.aspx?FamilyID=BE6A2C5D-00DF-4220-B133-29C1E0B6585F&displaylang=en

Thanks. This helps.

<snipped />
SQLExpress installs by only allowing trusted connections, thus you have
to manual re-enable at instal time or later, perhaps accessing via SSMSE
(http://www.microsoft.com/downloads/details.aspx?FamilyID=82AFBD59-57A4-455E-A2D6-1D4C98D40F6E&displaylang=en)
the "properties" of the running instance, selecting the "Security tab"
(ok, it's no longer a tab, but I hope you understand :D) and enabling
"SQL Server and Windows authentication mode"..
access then the "Security" node, "logins", select "sa" login (which is
there) and accessing it's "properties" enable it in the "Status" tab
(again, it's no longer a tab :D)

OK, I can do this on my local system and it works.


I have a series of questions, and if anyone can answer some or all of
these, I would be most appreciative.

1. How can I disable Windows users from connecting to or accessing
specific SQL Express databases, but permitting free access to others?


in the "Security" node, "logins", you can see all the registered logins,
both Windows accounts/groups, and SQL Server standard logins...
I do only have BUILTIN\Administrators, "sa", NT AUTHORITY\System
if you have further, like BUILTIN\Users, this imply all your local users
will be able to connect to your instance.. they have then to be granted
access to each database via a database user (and I think I already
pointed out that story)..
but are you perhaps using "User Instances"?
if this is the case, then everyone who executes your application will be
able to access the referenced database(s) as "user instances" ( or RANU
:D) are designed that way..
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsse/html/sqlexpuserinst.asp

I am not using user instances, so that is not the problem.

I am able to create the database, add the specific logins, define a
database user for each of the logins, and assign the default schema to the
database users, and then define the schema, and this is working.

I then establish a connection using the new login that is to control the
schema, and I add all of the tables and perform initial population, and
this is working.

I can connect to the database using the new login that is to access the
contents and I can create/update/delete/select data records, but I cannot
create or delete tables, as expected.

However, I can login as any Windows user and access and manipulate the
schema itself, as well as the database contents!

I cannot always control the installation of SQL Express. When my
application is to be installed, I can require that it be present, bu tif
it is already present, than I am stuck with the client installation
factors.

--> Can I force the 'sa' user to be enabled and valid from code? Can I
test for this somehow?

--> How can I prevent valid Windows users from accessing the contents of
my database, or at least the schema, from my code alone?

<snipped />


.

Relevant Pages

  • Re: Need help on how to organize users and objects
    ... Yes Oracle is a bit different than Sql Server, but I think you will get the ... It doesnt become part of any schema until you assign it. ... a new developer started to work. ... need the COMMON database which stores tables with generic data and generic ...
    (microsoft.public.sqlserver.security)
  • Re: MS Access DAO -> ADO.NET Migration
    ... William Vaughn ... Microsoft MVP ... Hitchhiker's Guide to Visual Studio and SQL Server ... My migration app works building a SSCE database file with imported data ...
    (microsoft.public.dotnet.framework.adonet)
  • xSQL Object 2.5.2.7
    ... synchronizing SQL Server databases. ... Server 2000 database with a SQL Server 2005 database and generate ... history of the schema changes and provide for safe and ... differences, view the scripts of objects, filter results and more; ...
    (comp.software.shareware.announce)
  • Re: Cluster will not fail over.
    ... > As far as the TCP/IP issue goes, you had to rebuild the cluster and were ... > able to restore the master database. ... > a cluster installation you'll have to revisit. ... >> This worked bringing up the sql server in minimal mode. ...
    (microsoft.public.sqlserver.clustering)
  • Re: MS Access DAO -> ADO.NET Migration
    ... For that it is much harder to handle the incremental identifier, ... database but although they have the data, they are not connected at the same ... The book was a pleasure to read after the gibberish that Microsoft 'puts ... SQL Server Management Studio is nowhere to be found on my ...
    (microsoft.public.dotnet.framework.adonet)