How to properly create Ms SQL login user?

[Fisher]

Hello!

I started created web app which will have MS SQL 2008 Expr back-end database. The Q is how to properly create user for SQL login which will be then used in SQL connection string. What proper credentials user has to have?

I noticed for example if I create SQL user with only "public" server role, then each table display in front "dbo." when I choose table from "Table/View name" dropdown list in grid builder. But if I also apply "sysadmin" role then I don't see this in front. This is also not visible when I choose Windows Authentication.

And problem is now if I select table each alias for table name is written in style "dbo_TableName" (if I have SQL login w/o sysadmin role). But in case that I have sysadmin role or Windows authentication then I see alias how I would like to see it (w/o dbo_ in front): "TableName", because table list does not contain that in front.

I would like to have universal approach which will work if I later change login from "SQL auth" to "Windows auth" or vice versa and table list or alias will stay the same.

What approach / recipe do you use, so you can at later time flawlessly change authentication type without any problems on existing components in A5 or new one?

Please if you can advise me on that dilemma. Thank you.


Fisher

[kkfin]

Re: How to properly create Ms SQL login user?

If your application needs to have remote connection then the choice is SQL Server Auth.

dbo before a table name is so called schema. It is a securable in SQL Server. Securables are objects that are accessed :server, database, schema.

Every users default schema is dbo. In SQL Server objects namespace is determined by schema: server.database.schema.object

This means that we can build objects and but them in namespaces of schemas and then change permissions of that schemas and automatically change permissions of all those objects.

So schema is a one step to make SQL Server more secure and easier to manage and has nothing to do with connection strings.

[Fisher]

Re: How to properly create Ms SQL login user?

Yes, I know it's schema, but why is then not also displayed in front if I use Windows auth? Schema is used here too, I think.

Do you also see in front "dbo" if you use SQL login settings? If not what rights you assign to the user so this not show up?. If I give "sysadmin" role to that user then this is not visible. but of course that I would not like to give if is not necessary.

And one more Q for you Ken, regarding that you are from Finland and I know you also have non ascii chars in your alphabet. Do you see all your chars (labels, your text, etc) on A5W or HTML pages created with Alpha5?

Fisher

[kkfin]

Re: How to properly create Ms SQL login user?

SQL Auth and Windows Auth are two different ways to login. They don't work identically. They have different rules. You cannot assign a default schema in SQL Server to a windows authenticated set of users. This behavior is by design. In fact the schema is there but it works different way-> MYDOMAIN\USERNAME

I thing the language support in Alpha is better than in previous versions but not in level it should be.

In very short it should be:

Alpha should have basic system language dictionaries for different languages.
When you create a new project you select default language(s) and charset.
Now all built in system messages works right with above selected languages.

Then you could create for you project a own language dictionary.
If you do that Alpha would create language variables for table names and field names for example for Phone field in Contacts table Alpha would create a lang_phone variable and you could give own values for above selected different languages.
After this Alpha would use this variable name in applications and different values according to selected language.

[bea2701]

Re: How to properly create Ms SQL login user?

SQL Auth and Windows Auth are two different ways to login. They don't work identically. They have different rules. You cannot assign a default schema in SQL Server to a windows authenticated set of users. This behavior is by design. In fact the schema is there but it works different way-> MYDOMAIN\USERNAME

I thing the language support in Alpha is better than in previous versions but not in level it should be.

In very short it should be:

Alpha should have basic system language dictionaries for different languages.
When you create a new project you select default language(s) and charset.
Now all built in system messages works right with above selected languages.

Then you could create for you project a own language dictionary.
If you do that Alpha would create language variables for table names and field names for example for Phone field in Contacts table Alpha would create a lang_phone variable and you could give own values for above selected different languages.
After this Alpha would use this variable name in applications and different values according to selected language.

Ken,

Give it a place on the <new>wishlist, lets see what the outcome is

Eric