Standard SQL Users & Permissions

Hello, List! Can someone point me to a document that lists the standard users with their default database and special permissions (system administrator, server administrator, etc.) created in MSSQL by OneWorld Xe during installation?

Muchas gracias, in advance!

-Tim

Tim,

I can't point you to the document, but I can give you an FYI that DBA's should be aware of - by default during a JDE install, the public role is given all permissions to all JDE database tables. And, even if you change this, each time a table is re-generated, it will default back to giving public all permissions.

We found out from JDE that this expected behavior, and it was up to us to change the permissions. We ended up writing a script to change them on a global scale and then and creating a process to manually change them on each table when we re-generate it.

We're using SQL Server 2000.

Yes, I have the same experience and we are using Oracle 8i.

Regards,
Alvin

Forgive my naivete. What do you have to change permissions to?

Well, you typically don't want public to have any permissions, other than perhaps read (we don't even want that), since, if anyone makes an ODBC connection to your databases, they'll have full access to read and write outside of JDE. So if you're like us, you'll want to remove all permissions.

You can do this either through the GUI (it would be a pretty arduous process when you are changing permissions in bulk) or through scripting. You do have to make sure that you've given the JDE user(s) necessary permissions before removing them from the public role, as by default, they are just inheriting whatever the public role has.

If you're new to SQL Server and don't have a DBA handy, I highly recommend reading about security and roles in the SQL Server Books Online. I could also send you the script we used to make the initial permission changes, but you're going to want to feel comfortable with the product before running such a script.