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

Globals.Referrer?

Regards,
We need to setup a report so that it only runs if the referrer is a certain
URL. This is so that end users cannot directly navigate to the report (or
if they know the URL, plop it into the address bar and navigate there).
Instead, we'd like the report to check if they're coming from a particular
application and run ONLY if it sees that it's coming from that application.
Any ideas?
Any help would be greatly appreciated!
Benjamin PierceI would
Try to write some code to override the on_init method of the report and
check for the referrer
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Benjamin Pierce" <bpierce@.opentext.com> wrote in message
news:uWIxXNyWFHA.3240@.TK2MSFTNGP10.phx.gbl...
> Regards,
> We need to setup a report so that it only runs if the referrer is a
> certain
> URL. This is so that end users cannot directly navigate to the report (or
> if they know the URL, plop it into the address bar and navigate there).
> Instead, we'd like the report to check if they're coming from a particular
> application and run ONLY if it sees that it's coming from that
> application.
> Any ideas?
> Any help would be greatly appreciated!
>
> Benjamin Pierce
>|||Is the On_Imit method part of SQL 2005 Reporting Services? I wasn't aware
of report methods in SQL 2000.
Let me know.
Thanks!
Ben
"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
news:%237pUQr4WFHA.2796@.TK2MSFTNGP09.phx.gbl...
> I would
> Try to write some code to override the on_init method of the report and
> check for the referrer
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "Benjamin Pierce" <bpierce@.opentext.com> wrote in message
> news:uWIxXNyWFHA.3240@.TK2MSFTNGP10.phx.gbl...
> > Regards,
> >
> > We need to setup a report so that it only runs if the referrer is a
> > certain
> > URL. This is so that end users cannot directly navigate to the report
(or
> > if they know the URL, plop it into the address bar and navigate there).
> > Instead, we'd like the report to check if they're coming from a
particular
> > application and run ONLY if it sees that it's coming from that
> > application.
> >
> > Any ideas?
> >
> > Any help would be greatly appreciated!
> >
> >
> > Benjamin Pierce
> >
> >
>

Global Server Variables

Is there a way in SQL Server to setup Global Server Variables or Constants

We are working with an Off the Shelf Constituent Management application based on SQL Server. We can only read from the App's DB. So we setup another DB to run SP to access the data in the main DB. One problem we have is that there codes that the app DB uses that we need to reference as criteria in our SP. Example: Code for a phone type of email is 731. So if we want to pull email addresses we need to Select where PhoneType = 731. We found out that each time the main db is rebuilt those codes change. That means finding everytime we used that code and changing it.

It would be great to be able to set a global variable and use it anytime that code is needed.

Any ideas.The way I have accomplished this myself is to use a UDF (user-defined function). This will give you just one place to make the change.

CREATE FUNCTION [dbo].[getPhoneType]
(
)
RETURNS int AS
BEGIN
RETURN 731
END

To use it:

SELECT * FROM myTable WHERE PhoneType = dbo.getPhoneType()

Terri|||Since you've created another db to hold your add-ons, you could build tables to hold those type of values. I usually create a name/value table to hold things like key/value pairs and then just select the correct record from it when I need it.