[thelist] MSDE - assigning user privileges

[Ken Schaefer]

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
From: "Paul Bennett" <paul at teltest.com>
Subject: [thelist] MSDE - assigning user privileges


: I just had an interesting question from a customer. At the moment we are
: developing a WAP application to query his sql server. Currently he is
: running the MSDE database server, not the full priced SQL Server version.
:
: Am I correct in understanding that in SQL Server, Enterprise Manager is
: used to view the database tables and schema and also set user privileges
: (updat, delete, insert etc)?
:
: Does a tool like this exist for MSDE?
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

MSDE ships with a command line tool (osql.exe) which you can use to execute
the system stored procedures that are used to assign user
rights/roles/permissions etc. Additionally, you can package up all the
commands into a .sql batch file, and pass that into osql.exe which will
execute the commands contained in the .sql file.

If you have a look at say, Service Pack 3 for MSDE/SQL Server you can see
the .sql files that are run (there are about 11 of them), one of which
spends a lot of time tweaking access to sprocs (the SQL Server team really
tightened up access to a lot of system sprocs in SP3)

Those same system sprocs are executed by Enterprise Manager (behind the
scenes) when you use EM to manage SQL Server or MSDE.

You can also do the same (running the sprocs) using Query Analyser if you
prefer.

Additionally, if you do have the SQL Server client tools (like EM, or QA)
you can point those at an MSDE installation, no problems.

To give you an idea of how it works, look up sp_addUser or sp_grantDBAccess
in SQL Server Books Online [1] - it shows you the syntax you use to add a
new user to a database.

Cheers
Ken

[1] http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp