Can I manually create the SQL tables?

Apr 7, 2011 at 7:27 PM

I have a fun issue... my Active Directory account is allowed dbo rights to the database I'm trying to SLAM to. I've got two other accounts for use within apps (in connection strings). One of them is read only, and the other allows for create/update/delete. Obviously neither account can create new tables. Since I can't put my AD account info in the connection string (and would never do so anyway), I don't have a way for SLAM to create the tables during activation.

What I'd like to do is this:

Since my AD account has dbo rights, I can manually create the table and all its columns. Then I can specify my sql user account that has create/update/delete permissions so that SLAM can populate the table.

The question is, will I be able to activate SLAM with that user or will it always try to create the tables?

Any other ideas short of asking for a dbo account from the DBA's?

Thanks!

Developer
Apr 7, 2011 at 7:41 PM

SLAM will only try to create the tables if they don't exist.  So what you are suggesting should work fine.

Another option would be to use SQL Server Authentication instead of integrated.

Apr 7, 2011 at 7:55 PM

Thanks for the reply...

The two user accounts I mentioned are SQL Server Accounts, but they are limited to read only, and the other is limited to CrUD only with existing tables.

Here's what I just tried... maybe I've got something configured wrong. I created a table called tblRequests in my SQL database. I created the columns and set the Data Types to exactly what I have set in my slam.config... here's what that table looks like:

id        - int         - not null
Title     - varchar(50) - not null
strUUID   - varchar(50) - null
strStatus - varchar(50) - null
For now I'm keeping the table simple. Once it's working, then I'll complicate it by adding the other columns. Here's my slam.config file:

<?xml version="1.0" encoding="utf-8" ?>
<Configuration>
  <ConnectionStrings Default="SLAM">
    <add Name="SLAM" ConnectionString="Database=DATABASENAME;Server=SERVERNAME;User Id=USERID;Password=PASSWORD" />
  </ConnectionStrings>
  <DataMapping DataSchema="SLAM">
    <List Site="/CIM" Name="Requests" TableName="tblRequests" ActivationOrder="1">
      <Fields>
        <Field Name="Title" SqlType="varchar(255)" SPType="Text" Required="true"></Field>
        <Field Name="strUUID" SqlType="varchar(50)" SPType="Text"></Field>
        <Field Name="strStatus" SqlType="varchar(50)" SPType="Text"></Field>
      </Fields>
    </List>
  </DataMapping>
</Configuration>

As far as I can tell, everything is setup right. I've specified the table name I used with TableName="tblRequests" and the Field SPType's match up with the list correctly. Everything validate's fine. However, when I now try to activate, I get this error:

User does not have permission to perform this action.
CREATE SCHEMA failed due to previous errors.   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
   at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
   at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
   at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async)
   at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)
   at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
   at System.Data.Linq.SqlClient.SqlProvider.Execute(Expression query, QueryInfo queryInfo, IObjectReaderFactory factory, Object[] parentArgs, Object[] userArgs, ICompiledSubQuery[] subQueries, Object lastResult)
   at System.Data.Linq.SqlClient.SqlProvider.ExecuteAll(Expression query, QueryInfo[] queryInfos, IObjectReaderFactory factory, Object[] userArguments, ICompiledSubQuery[] subQueries)
   at System.Data.Linq.SqlClient.SqlProvider.System.Data.Linq.Provider.IProvider.Execute(Expression query)
   at System.Data.Linq.DataContext.ExecuteMethodCall(Object instance, MethodInfo methodInfo, Object[] parameters)
   at System.Data.Linq.DataContext.ExecuteCommand(String command, Object[] parameters)
   at AWS.SLAM.Data.SqlLogDataRepository.CreateSchema(String schemaName)
   at AWS.SLAM.Logging.SqlLogger..ctor(IConfigurationManager configuration)
   at AWSSLAMLoggingSqlLoggerInstanceBuilderac45e21ab87040c78c14dffcca16e9bc.BuildInstance(IConfiguredInstance , BuildSession )
   at StructureMap.Pipeline.ConfiguredInstanceBase`1.StructureMap.Pipeline.IConfiguredInstance.Build(Type pluginType, BuildSession session, InstanceBuilder builder)
It looks like it's still trying to create a table still?

Developer
Apr 7, 2011 at 7:59 PM

Try this: temporarily give the user full access to the DB.  Activate SLAM.  Then downgrade the user's permissions.

Apr 7, 2011 at 8:03 PM

I've got a request in to our DBAs for an account with more permissions - but being such a large company, nothing happens fast around here. Heheh... that will probably take a week or so. I'm in no rush to have this up and working, but was hoping to find a way around for now... but if there's no other option, that's not a big deal. I can wait for the account from the DBAs.  :)

Thanks!

Apr 8, 2011 at 8:04 PM

They went ahead and setup my write account with dbo access to that database, so I was able to get setup and syncing. I'm now seeing another error, but I'll reply to another thread that's having the same problem.

 

Thanks!