Re: Creating SQL Server Databases With No Security Restrictions

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: Andrea Montanari (andrea.sqlDMO_at_virgilio.it)
Date: 02/08/05 

Date: Tue, 8 Feb 2005 18:47:06 +0100

hi Peter,
Peter van der Goes wrote:
> First, let me apologize for crossposting and the lengthy post, but
> this question involved both MSDE and (I believe) security settings.
> We use Visual Studio .NET 2003 with MSDE in classrooms and labs and
> have had no security issues once things were configured properly for
> student access. The sample databases install and work correctly for
> any authorized users who might be logged on to a given PC.
> We are using a text that supplies .mdf files as part of the support
> materials for the book. We are able to copy the .mdf files to the
> appropriate directory (..\Microsoft SQL
> Server\MSSQL$InstanceName\Data), then use Server Explorer in VS .NET
> to create a database link to the file (Connect To Database button on
> the Server Explorer toolbar). Once VS .NET is restarted, the database
> appears in the list of databases under the SQL Server instance and is
> fully accessible.
> I now want to create additional SQL Server databases for students to
> use with development projects, etc. I have access to the SQL Server
> GUI management tools (from our MSDNAA subscription) and they are
> installed and working with the MSDE instance on my office PC. When I
> create a database/tables/etc. on my instance, then attempt to copy
> the .mdf to another PC to use the setup technique described above, a
> window pops up wanting me to select a security option and then
> refuses me access to the new database regardless of the option (NT
> Security or database username/password) I try to choose.
> After all that, what I'm seeking is the way to create a database to
> export to any PC, with any student or faculty member logged on so
> that they can import the .mdf file and connect to it using Server
> Explorer *without* encountering security issues. So, when I'm
> creating a database, what settings do I apply so that it doesn't care
> who is interacting with it?
>
> Thanks in advance for reading and any insights!

in order to attach an existing db file, as long as create a brand new
database, the logged user must be member of the sysadmin and/or dbcreator
server role...
in order to connect to an MSDE instance you have to provide a login
information, both for Windows NT (trusted) authenticated or SQL Server
authenticated connections, as you provide this kind of information in the
Data Link dialog to (Connect To Database button on the Server Explorer
toolbar).... so there's no difference in the behaviour you are
experimenting..
what kind of error do you get when accessing the attached database? 

-- 
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtm        http://italy.mvps.org
DbaMgr2k ver 0.10.0  -  DbaMgr ver 0.56.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
--------- remove DMO to reply

Relevant Pages

  • Idiomatic Expressions to Operating System Architecture: Transforming Rigids
    ... <Subject: Re: Idiomatic Expressions to Operating ... <endpoint during a single session * DATABASE + by ... <statements is a new feature of SQL Server 2005. ...
    (sci.math)
  • Re: SQL or Access DB
    ... As far as encryption goes though... ... with Sql Server you can use SQL DMO and encrypt your stored procedures ... installation - Security was absolutely critical and in most instances, ... > then we create a nice gui around this database and sell it to automotive ...
    (microsoft.public.dotnet.languages.vb)
  • Re: Cryptography in SQL Server 2000
    ... SQL Server 2000 Out-of-the-Box database security solution. ... database protection and privacy software is an industry-proven ...
    (microsoft.public.sqlserver.security)
  • Re: SQL or Access DB
    ... i am aware of the security modell of SQL server (we do use SQL server ... an end user,,, there is not reall alternative as a worgroup Access database ... MSDE and SQL express are all free ...
    (microsoft.public.dotnet.languages.vb)
  • Re: Cryptography in SQL Server 2000
    ... A company is vulnerable when its security ... > database encryption solution with protected key-management software ... > tested by the SQL Server Test Lab. ...
    (microsoft.public.sqlserver.security)