Granting User Privileges??

I have setup an SQL MSDE instance on my PC and I currently finished working
on the BE in an access 2003 project. I believe it is now time to define
roles, users and grant each one its privileges, but how do I do that?
I know about GRANT, REVOKE etc, but
1. Where do I type these?
2. Is all this information encrypted?
3. When installing the MSDE I set a password what is this all about? Will
it have any impact on user level security?
4. Is there a visual tool to handle user level security, such as in an
Access database?
5. As I indend to create a distributed database application is these
security scheme in accordance to that?
6 and final. How can I let the superuser (aka the superior user of the
client) grant privileges to other users, after granting him the option to
grant? eg form?
Some answers inline
"Dimitrios Tanis" <jtanis@.mycosmos.gr> wrote in message news:O%23XXjhBnFHA.3608@.TK2MSFTNGP15.phx.gbl...
>I have setup an SQL MSDE instance on my PC and I currently finished working on the BE in an access 2003 project. I
>believe it is now time to define roles, users and grant each one its privileges, but how do I do that?
> I know about GRANT, REVOKE etc, but
> 1. Where do I type these?
In a script, run with osql.exe against the database is one way.
Query Analyser from a developer pc or pc with sql server installed is another

> 2. Is all this information encrypted?
Not unless it is embedded in your application, and only run locally or through an encrypted connection.
Even if it was scripting the database will get this configuration type of information back. The encyption in
SQL Server 2000 / MSDE is for view, triggers and stored procedures - i.e. code, and is crackable anyway.

> 3. When installing the MSDE I set a password what is this all about? Will it have any impact on user level security?
Depends on what authentication mode you use - with windows authentication, the only password you
have to worry about is the sa one on the install, and this is only a problem if someone at some stage
changes the security to SQL/Mixed.

> 4. Is there a visual tool to handle user level security, such as in an Access database?
If you map windows groups to the SQL database roles, then your user management is done under windows itself.
The beta for SQL 2005 Express includes a GUI tool, and there is a web based one for 2000.
http://www.microsoft.com/downloads/d...displaylang=en
http://www.microsoft.com/downloads/d...DisplayLang=en
Apart from them there is osql.exe for the masochistic, Enterprise Manager for the licensed, or your application
doing sql calls behind the scenes.

> 5. As I indend to create a distributed database application is these security scheme in accordance to that?
Well you are going to have to enable the network libraries, which immediatly gives you an order of magnitude
more security worries. For example a breach in your install of MSDE can grant full access to the PC (xp_cmdshell).
Integrated security is Microsoft's recommendation, but you still need to think about changing network ports and
firewalling.

> 6 and final. How can I let the superuser (aka the superior user of the client) grant privileges to other users, after
> granting him the option to grant? eg form?
see 4)
also suggest reading security related info from
http://msdn.microsoft.com/library/de...stsql_84xl.asp
Regards
AJ

Going to SQL7 DE to SQL 2000 MSDE

Hi All
Does anyone know if there is a path to change a database
from MSSQL 7 Desktop Edition (note, not desktop engine) to
MSDE 2000 ?
Thanks
JI very much doubt that there's some setup program that will upgrade the 7.0
DE to 2000 MSDE. My guess is that you have to use backup/restore or
detach/attach - something that you can't do for the system databases (they
can't cross editions).
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"J" <anonymous@.discussions.microsoft.com> wrote in message
news:05c401c3bed1$a6597a30$a401280a@.phx.gbl...
> Hi All
> Does anyone know if there is a path to change a database
> from MSSQL 7 Desktop Edition (note, not desktop engine) to
> MSDE 2000 ?
> Thanks
> J|||Hi
Do you recommend that I first update the database to SQL
2000 and then do a backup and restore into MSDE?
Do you know where I can find documentation on restoring
into MSDE?
Thanks
J
>--Original Message--
>I very much doubt that there's some setup program that
will upgrade the 7.0
>DE to 2000 MSDE. My guess is that you have to use
backup/restore or
>detach/attach - something that you can't do for the
system databases (they
>can't cross editions).
>--
>Tibor Karaszi, SQL Server MVP
>Archive at:
>http://groups.google.com/groups?
oi=djq&as_ugroup=microsoft.public.sqlserver
>
>"J" <anonymous@.discussions.microsoft.com> wrote in message
>news:05c401c3bed1$a6597a30$a401280a@.phx.gbl...
>> Hi All
>> Does anyone know if there is a path to change a database
>> from MSSQL 7 Desktop Edition (note, not desktop engine)
to
>> MSDE 2000 ?
>> Thanks
>> J
>
>.
>|||I'm not sure. I haven't dealt with MSDE so I don't know about all the setup
quirks that MSDE has. But upping to Personal Edition 2000 first sounds like
a good idea (make it the same service pack as well), because you now have
the option to restore both system and user databases as well. The restore
process is the same for a regular SQL Server, the RESTORE command. I suggest
you post to the msde group, as you might find more specific suggestions
there.
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"J" <anonymous@.discussions.microsoft.com> wrote in message
news:1147a01c3bf5f$63eb4ff0$a601280a@.phx.gbl...
> Hi
> Do you recommend that I first update the database to SQL
> 2000 and then do a backup and restore into MSDE?
> Do you know where I can find documentation on restoring
> into MSDE?
> Thanks
> J
>
> >--Original Message--
> >I very much doubt that there's some setup program that
> will upgrade the 7.0
> >DE to 2000 MSDE. My guess is that you have to use
> backup/restore or
> >detach/attach - something that you can't do for the
> system databases (they
> >can't cross editions).
> >
> >--
> >Tibor Karaszi, SQL Server MVP
> >Archive at:
> >http://groups.google.com/groups?
> oi=djq&as_ugroup=microsoft.public.sqlserver
> >
> >
> >"J" <anonymous@.discussions.microsoft.com> wrote in message
> >news:05c401c3bed1$a6597a30$a401280a@.phx.gbl...
> >> Hi All
> >> Does anyone know if there is a path to change a database
> >> from MSSQL 7 Desktop Edition (note, not desktop engine)
> to
> >> MSDE 2000 ?
> >> Thanks
> >> J
> >
> >
> >.
> >

Going from osql to sqlcmd (MSDE to SqlExpress?)

I am familiar with loading a DB so that it will work under MSDE. This
is the command I use:
osql -S ServerName\InstanceName -E -i ScriptName.sql
So now I uninstalled MSDE and am using SqlExpress instead. To do the
same I tried:
Sqlcmd -S .\SQLExpress -i ScriptName.sql
and
Sqlcmd -S ServerName\SQLExpress -E -i ScriptName.sql
Which does not do anything except give me an erro stating that my DB
does not exist under SQLExpress. (Which is why I was trying to
install it )
Am I missing something? Do I need MSDE running on my toolbar?
How do I load a DB into SQLExpress?
Regards
oracle wrote:
> I am familiar with loading a DB so that it will work under MSDE. This
> is the command I use:
> osql -S ServerName\InstanceName -E -i ScriptName.sql
> So now I uninstalled MSDE and am using SqlExpress instead. To do the
> same I tried:
> Sqlcmd -S .\SQLExpress -i ScriptName.sql
> and
> Sqlcmd -S ServerName\SQLExpress -E -i ScriptName.sql
>
Those commands do not load a database. They connect to the specified SQL
Server instance. The SQL service is already running and the databases
are mounted and ready. It sounds like the problem you are having is that
SQL Express does not know about your MSDE databases. How did you move
the databases to SQL Express? If you didn't move them, you might be able
to use the "CREATE DATABASE database_name FOR ATTACH" command. If you
still have MSDE installed, first detach the databases using sp_detach_db
and copy them over the SQL Express data folder. I do not know if you can
do this from SQL Express Manager.
David Gugick
Quest Software
www.quest.com