prevent user from creating database

amphysvena

Hi,
Currently our users has been set the role of USER. they have no permission to test, alter, delete, & see database information.
But the problem is they still can create new database. How to prevent the user from creating new database?
I've checked in Role -> Permission. But can't find any clue.
Any solution?
Thanks

NaviDev

If you are using SQL, u can set it up on the security. If you are using native database and using AD, AFAIK u can set it on the policies.

amphysvena

I'm using SQL 2005. Could you specify how to set the security in SQL?
I'm guessing, i need to set the correct server role for the user. Right now, i've set sysadmin for all of the user.
Thanks for the reply

kine

The problem is, that after you open client, you are not conencted to any database by default and thus you have no point where to define permission for the user. It means he can create new database, another question is, where he can create new database... :-) and why... and what he will do with this database...

NaviDev

To amphysvena,

Theres a lot of documents out in the internet you can view regarding SQL Security like this http://articles.techrepublic.com.com/5100-6329-1061781.html

kine wrote:

The problem is, that after you open client, you are not conencted to any database by default and thus you have no point where to define permission for the user. It means he can create new database, another question is, where he can create new database... :-) and why... and what he will do with this database...

And you are referring to native? Writing scripts on AD can handle user restrictions right?

garak

if you use windows auth. only administrators or DBCreators (SysAdmins) can create databases. So check, if your user is an member of admins

http://msdn2.microsoft.com/en-us/library/ms175892.aspx

http://msdn2.microsoft.com/en-us/library/ms187648.aspx

amphysvena

Hi,
I've removed any server roles from a user. So they can't create new database. But the problem is, when they create new line in the form Post Dated Check - Sales (Form 28090)
It raised error :
8104,"42000",[Microsoft][ODBC SQL Server Driver][SQL Server]The Current user is not the database or object owner of table 'XXX$Post Dated Check Line'.Cannot perform SET operation.
But it solve the problem if the user is given sysadmin server role.
Many thanks for the replies.

kine

amphysvena wrote:

Hi,
I've removed any server roles from a user. So they can't create new database. But the problem is, when they create new line in the form Post Dated Check - Sales (Form 28090)
It raised error :
8104,"42000",[Microsoft][ODBC SQL Server Driver][SQL Server]The Current user is not the database or object owner of table 'XXX$Post Dated Check Line'.Cannot perform SET operation.
But it solve the problem if the user is given sysadmin server role.
Many thanks for the replies.

This error is problem of wrong C/AL code, where the code is not clearing PK field when insertin record into table with AutoIncrement field. It means, if you are inserting into table with AutoIncrement, you need to insert it with 0 in the PK, else you will get this error. And do not forget that INIT is not clearing PK fields... ;-)

amphysvena

kine wrote:

This error is problem of wrong C/AL code, where the code is not clearing PK field when insertin record into table with AutoIncrement field. It means, if you are inserting into table with AutoIncrement, you need to insert it with 0 in the PK, else you will get this error. And do not forget that INIT is not clearing PK fields... ;-)

Thx,
But I'm a lousy programmer. I can't find the code to insert into the Post Dated Check Line table. Where do I have to put the code to clear the PK? On insert trigger? on new record trigger?
Thx a lot

kine

Just turn the debugger on, simulate the error, you will see where the .INSERT is called. In the section before the insert just clear the PK field.

amphysvena

kine wrote:

Just turn the debugger on, simulate the error, you will see where the .INSERT is called. In the section before the insert just clear the PK field.

Thank you for the reply.
But I've already turn the debugger on. & the error just pop up without goes to NAV debugger. Thx

kine

Ah, you are entering the new record manually - than you need to disable "AutoSplitKey" on the form, because it is trying to use the automatic line numbering (10000, 20000 etc.)

amphysvena

kine wrote:

Ah, you are entering the new record manually - than you need to disable "AutoSplitKey" on the form, because it is trying to use the automatic line numbering (10000, 20000 etc.)

Ok, I'll disable the "AutoSplitKey" on the form. But then how I make the line still generating the running number? (10000, 20000, 30000,...)
Sorry for my stupid question :oops:
Thanks

kine

If you want to use the numbering in a way used on e.g. Sales Order lines, you need to keep the AutoSplitKey = Yes but you need to disable AutoIncrement property on the field in the table. You cannot use both ways on one table... 8)

sajidhanifkhan

I've removed any server roles from a user. So they can't create new database. But the problem is, when they create new line in the form Post Dated Check - Sales (Form 28090)
It raised error :
8104,"42000",[Microsoft][ODBC SQL Server Driver][SQL Server]The Current user is not the database or object owner of table 'XXX$Post Dated Check Line'.Cannot perform SET operation.
But it solve the problem if the user is given sysadmin server role.

You should not give users Sysadmin roles. Seems u restore a sql backup and started using as live database with all previous listed users. To remove such error you can do two ways;
1. make a new database , restore the latest backup delete the users from the user table and assign sql databse users to the new created databse and then create them again in Navision newly created databse and synchornise them with sql server.

2. Simply remove the user from navision and create them again and synchronise it with sql.

Hope this will help to solve your proble.

regards
Sajid

kine

sajidhanifkhan wrote:

I've removed any server roles from a user. So they can't create new database. But the problem is, when they create new line in the form Post Dated Check - Sales (Form 28090)
It raised error :
8104,"42000",[Microsoft][ODBC SQL Server Driver][SQL Server]The Current user is not the database or object owner of table 'XXX$Post Dated Check Line'.Cannot perform SET operation.
But it solve the problem if the user is given sysadmin server role.

You should not give users Sysadmin roles. Seems u restore a sql backup and started using as live database with all previous listed users. To remove such error you can do two ways;
1. make a new database , restore the latest backup delete the users from the user table and assign sql databse users to the new created databse and then create them again in Navision newly created databse and synchornise them with sql server.

2. Simply remove the user from navision and create them again and synchronise it with sql.

Hope this will help to solve your proble.

regards
Sajid

This will not solve your problem. The source is the Autoincrement used in wrong way. Have you managed to solve your problem? Have you some new results of your testing?

amphysvena

Hi all,
Sorry for the late reply. Thanks a lot for the replies. I really, really appreciate it. But right now, I'm in charge in two projects. & we are facing some serious problem with locktable in SQL. I'm focusing to the locktable issue first. after finish with the locktable, I'll check the autoincrement problem. Soon after that i'll post the test result. Once again, thank you for the assistance.

amphysvena

Solve the problem.
Correct this is an autoincrement problem. Just set the property of field "Line Number" in Table "Post Dated Check Line". Set the property AutoIncrement = No
So right now, I don't give the user the Sysadmin role, so they can't create, delete the database. Thanks for all the assistance