Hi Charitha, In this particular scenario, it looks like you haven't properly configured the database user being used, with "SELECT" privileges. Maybe you did, but there's a possibility that it got overridden by some other setting as well. Let me analyse the set of permissions required for SQL Server scripts to run and let you know.
On a side note, IMO, -Dsetup is a useful feature when it comes to deploying the products in some environment like "Dev" (to make it easy for users to simply install the product, configure the schema and use it for dev tasks), etc but, not when you deal with "Prod" kind of environments where the access to resources are typically restricted. In other words, it's usually the task of a database administrator to create databases, create schema objects, users, other database entities in a properly managed storage provisioning environment (even for environments like "Dev", etc depending on the organizational policies). Therefore, generally, as it is with any other production deployed entity, POLP (Principle of Least Privileges) is practiced by those database administrators to restrict users to get themselves involved in tasks like altering database schemas etc even accidentally as human errors. So, the general practice is that, an application should be able to survive with the basic set of permissions required for performing CRUD operations like, SELECT, INSERT, UPDATE, DELETE + other relevant permissions to execute procedures/routines, etc depending on the requirement. Because, if some database application tries to create/alter database schema objects, it usually is an indication of poor separation of concerns and hence, has to be avoided. I've seen this in some places such as registry, identity, etc related components which need to be fixed. Cheers, Prabath On Tue, May 6, 2014 at 1:20 AM, Charitha Kankanamge <chari...@wso2.com>wrote: > Hi folks, > I had to assign "sysadmin" server role to the registry/um DB user to start > the carbon server with -Dsetup in MSSQL server. With lesser privileges, I'm > getting the following error. > > java.lang.Exception: Error occurred while executing : IF NOT EXISTS > (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = > OBJECT_ID(N'[DBO].[REG_CLUSTER_LOCK]') AND TYPE IN (N'U')) CREATE TABLE > REG_CLUSTER_LOCK ( REG_LOCK_NAME VARCHAR (20), REG_LOCK_STATUS VARCHAR > (20), REG_LOCKED_TIME DATETIME, REG_TENANT_ID INTEGER DEFAULT 0, PRIMARY > KEY (REG_LOCK_NAME) ) > at > org.wso2.carbon.utils.dbcreator.DatabaseCreator.executeSQL(DatabaseCreator.java:169) > at > org.wso2.carbon.utils.dbcreator.DatabaseCreator.executeSQLScript(DatabaseCreator.java:325) > at > org.wso2.carbon.utils.dbcreator.DatabaseCreator.createRegistryDatabase(DatabaseCreator.java:61) > at > org.wso2.carbon.user.core.common.DefaultRealmService.initializeDatabase(DefaultRealmService.java:278) > ... 19 more > Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: The SELECT > permission was denied on the object 'objects', database > 'mssqlsystemresource', schema 'sys'. > at > com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(Unknown > Source) > at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(Unknown > Source) > at > com.microsoft.sqlserver.jdbc.SQLServerStatement.doExecuteStatement(Unknown > Source) > at > com.microsoft.sqlserver.jdbc.SQLServerStatement$StmtExecCmd.doExecute(Unknown > Source) > at com.microsoft.sqlserver.jdbc.TDSCommand.execute(Unknown Source) > > What are the minimum set of permissions that must be granted to the DB > user when creating MSSQL database? Please advise. We should update [1] with > the exact set of permissions. > > [1]https://docs.wso2.org/display/AM160/Setting+up+with+MS+SQL > [2]https://wso2.org/jira/browse/DOCUMENTATION-801 > > Thanks! > Charitha > -- Prabath Abeysekara Associate Technical Lead, Data TG. WSO2 Inc. Email: praba...@wso2.com Mobile: +94774171471
_______________________________________________ Dev mailing list Dev@wso2.org http://wso2.org/cgi-bin/mailman/listinfo/dev
